Powershell 4 - JAMF - REST eliminate multiple queries to get subsets

Shadow Breeze
Shadow Breeze used Ask the Experts™
on
Please reference the solution to my previous question:

PowerShell:  Move from using arrays/looping into objects/pipelining

This has been working on the inital data sets but these were quite small.  Now that the QA team is actually loading devices, the script fails to write the results to the file.  Here is the current code:
# Get all mobile devices from JAMF
$JAMFfile = $FilePath + "\JAMF_Prod.csv"

$BaseURL = "https://" + $JAMFAPIServer + "/JSSResource/mobiledevices"
$Header = @{"Authorization" = "Basic "+[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($JAMFAPIUser+":"+$JAMFAPIPassword))}
$Type = "application/json;charset=UTF-8"


Try 
{

    $MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header
    $MobileDeviceResponse.mobile_devices.mobile_device |
        Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty department}},
                         @{n="building";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} |
        Where-Object {$_.name -eq $_.serial_number} |
        Export-CSV -Path $JAMFfile -NoTypeInformation
}
Catch 
{
    Get-Date -Format g
    $_.Exception.ToString()
    $error[0] | Format-List -Force | Out-File "Errors.txt" -Encoding utf8 -Append
} 

Open in new window


The 'JAMF_Prod.csv' file is created as zero bytes.
If I display the '$MobileDeviceResponse.mobile_devices' to the console, it immediately shows the results.  But nothing is written to the file and no errors are captured.

Before this failed, I noticed it was taking about 2 minutes to complete the file update while the console output was just a few seconds.  This was with only about 150 devices.

Is there a better way of handing this than piping the REST results to 'Export-CSV -Path $JAMFfile -NoTypeInformation' ?

I will continue to troubleshoot this on my own but any assistance will help as this is moving into production.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
Probably changing line 13 on for:
 $MobileDeviceResponse.mobile_devices |
        Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty department}},
                         @{n="building";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} |
        Where-Object {$_.name -eq $_.serial_number} |
        Export-CSV -Path $JAMFfile -NoTypeInformation

Open in new window


or

Post the result of the variable with 1 device so we can actually try to recreate the problem if my previous suggestion doesn't' work.
$MobileDeviceResponse.mobile_device1

Author

Commented:
It seems the issue has change from just not writing to the file but slow performance .  It is inconsistent.  It is taking 8 minutes to pull down about 1000 records.  

Sample record:

name          	: 8XR8D6JF
id           		: 950
serial_number : 8XR8D6JF
model         	: iPad 6th Generation (Wi-Fi)
department    : 02688
building      	: James Rose 1
inactive      	: 

Open in new window


Changing the code make no difference.  I have 2 environments and only changed 1.  They both completed.  Both took the same amount of time.  But I don't know if it fails until it completes that section and writes to the file or the screen.  And now the output to the  screen only is just as slow.

I will try to try other filters and see how that affects it.  Let me know what you think.
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
What if you try to run the script until this line:

$MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header

Problem is that piping several objects will take time, especially in a 3 or more pipes sublevels.

Author

Commented:
What I've tried is to remove the Export-CSV call and then after the REST call completes, I loop through the variable and send it out to a text file.
This is blazing fast (1-2 seconds) - but it is unfiltered.

Try 
{
    
    $MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header
    $MobileDeviceResponse.mobile_devices.mobile_device |
        Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty department}},
                         @{n="building";e={ 
                                (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location |
                                 Select-Object -ExpandProperty building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} |
        Where-Object {$_.name -eq $_.serial_number} #|
        #Export-CSV -Path $JAMFfile -NoTypeInformation
}
Catch 
{
    Get-Date -Format g
    $_.Exception.ToString()
    $error[0] | Format-List -Force | Out-File "C:\Errors.txt" -Encoding utf8 -Append
} 
Finally
{
    $Time=Get-Date
    "Finished JAMF lookup: This script made a read attempt at $Time" | out-file "C:\JAMFconnect.txt" -append
}
Write-Output "Starting export JAMF"
foreach($device in $MobileDeviceResponse.mobile_devices )
{
   $device | Out-File "C:\JAMF_Out.txt" -Encoding utf8 -Append 
} 

Open in new window


Yields: (sample)

id               		: 3
name             	: iPad
device_name      : iPad
udid             		: 8babc61b94a51a957dd227208f307f973
serial_number    	: PTDD1ZHL
phone_number    : 
wifi_mac_address : 00:00:00:00:00
managed          	: True
supervised       	: True
model            	: iPad 5th Generation (Wi-Fi)
model_identifier : iPad6,11
modelDisplay     : iPad 5th Generation (Wi-Fi)
model_display    : iPad 5th Generation (Wi-Fi)
username         : 

Open in new window


This seems so obvious.  But if I use $MobileDeviceResponse.mobile_devices.mobile_device - the results are empty.
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
The problem is that you're not doing a single Call to the API
you do one to get the "results on $MobileDeviceResponse variable"
that's the only call to the API that you should do.

then you just use the object $mobileDeiveResponse to get the rest of the properties.

so if there are 1k entries you are doing  x3 calls (3k calls to the API in total) which is insanely wrong.
That's why I asked you to post 2 or 3 results on the $mobileDeviceResponce variable.
so we can actually work from there.
Sketch.png

Author

Commented:
I'm sorry if I'm misunderstanding you, but the previous sample _is_ $MobileDeviceResponse.mobile_device[1].  I understand it is 4 calls per record because we need the id from the first call to navigate the subsets.  I originally thought the issue was with the Export-CSV, but it's clear it's a performance issue.

I just notice that the original link to the JAMF developer site is no longer working..
Top Expert 2014

Commented:
One item that you may want to double-check if you're getting a zero-byte .CSV, is the Where-Object filter before the Export-CSV command.
Where-Object {$_.name -eq $_.serial_number}
In the examples you've shown so far, this has always been false and would result in all results being filtered out.

One optimization I see that appears could be made to eliminate one call to the API (per loop), is as below:
    $MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header
    $MobileDeviceResponse.mobile_devices.mobile_device | ForEach-Object `
    {
        $locationInfo = (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location
        $_ | Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={$locationInfo.department}},
                         @{n="building";e={$locationInfo.building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} 
    } | Where-Object {$_.name -eq $_.serial_number} |
       Export-CSV -Path $JAMFfile -NoTypeInformation

Open in new window


You can test that it's working with
    $MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header
    $MobileDeviceResponse.mobile_devices.mobile_device[0..2] | ForEach-Object `
    {
        $locationInfo = (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location
        $_ | Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={$locationInfo.department}},
                         @{n="building";e={$locationInfo.building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} 
    }

Open in new window

Even if the results of that block are slow or if there is a failure in the secondary REST API calls, as long as the first command succeeded then you should see results.

Author

Commented:
Thanks for the follow up!

First, sorry for the oversight on the example I provided.  There are definitely matches in the results where the name = serial number.  I was backtracking at some point to see if it was faster to eliminate the filter at that point and just took the first one back.

Second, running your example does bring back _all_ the records with the subset fields included.  It is taking a few minutes less, which is good as there are now several hundred more records than when I first posted.  So, I'm going to start a second line of attack of accepting this data set and then filtering it locally for the ones where the name matches the serial_number.

Your example made me think that I should revisit the initial question before this one and see how saving the id and top level fields only and then looping through those as completely separate queries against the subsets.  At least to check the speed.

Any further ideas will be appreciated.
Top Expert 2014

Commented:
Yes, always best to filter as early as possible.
    $MobileDeviceResponse = Invoke-Restmethod -Uri $BaseURL -Method GET -ContentType $Type -Headers $Header
    $MobileDeviceResponse.mobile_devices.mobile_device | Where-Object {$_.name -eq $_.serial_number} | ForEach-Object `
    {
        $locationInfo = (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/location" -Method GET -Headers $Header).mobile_device.location
        $_ | Select-Object -Property name, 
                         id,
                         serial_number,
                         model,
                         @{n="department";e={$locationInfo.department}},
                         @{n="building";e={$locationInfo.building}},
                         @{n="inactive";e={ 
                                 (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/extended_attribute.extended_attribute" -Method GET -Headers $Header).mobile_device.extended_attribute.extended_attribute |
                                  Select-Object -ExpandProperty Inactive}} 
    } | Export-CSV -Path $JAMFfile -NoTypeInformation

Open in new window


Beyond that, the only other potential improvement I can see would be if the service allows batch queries, i.e. submitting more than one ID in a single request.  However, that typically brings along other complications because the results come back as a set.  It's much easier to merge two single objects than it is to merge two sets of objects.

Author

Commented:
Thank you!  I will try this as soon as possible.  I have a few deadlines to meet today.

Author

Commented:
Update:  Moving the Where-Object closer to the source makes a huge improvement!  I am so used to writing SQL that I automatically place a WHERE clause after a SELECT and FROM.

More importantly, I went back to the API documentation and then on-line communities and found I can merge the subset nodes into one URI:

$locationInfo = (Invoke-Restmethod -Uri "$BaseURL/id/$($_.id)/subset/General%26Location%26mobiledeviceextensionattributes" -Method GET -Headers $Header)

Open in new window


This means I now only have 2 queries.  The first to get the id's of all the records (which I am writing to a CSV file for now) and the second to loop through the new query using those id's.

I am working on the best way to capture this and then export just the fields I need.

I will try to update soon but I still have other deadlines to meet.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial