I am working with the Microsoft Graph API, and trying to produce an app device install status report from Intune. The intent is to provide an Intune application ID, and get a list of devices with the application installed.
Using Invoke-RestMethod, I am able to retrieve the same data that is shown in the Intune portal, but the resulting JSON is not in what I would class as an ideal format for manipulating with Powershell (at least based on my current knowledge of working with PowerShell).
For example, the resulting object from Invoke-RestMethod looks like this:
TotalRowCount : 923
Schema : {@{Column=AppInstallState; PropertyType=String}, @{Column=AppInstallState_loc; PropertyType=String}, @{Column=ApplicationId; PropertyType=String}, @{Column=DeviceId; PropertyType=String}…}
Values : {S1 Installed 39c23e0b-0098-4eb4-9613-232a005eee82 5010bc04-d22f-46ba-94bb-4bd3213ad13f CPC-james-NSYIT 1 82633388-108c-4ea9-842e-ff9a849f5159 Allison, James james.allison@ukpowernetworks.co.uk, S1 Installed 39c23e0b-0098-4eb4-9613-232a005eee82 f8ec0b7a-044a-4098-be27-b680072a3b83
FD-2do7AN5hEMv2 1 b7c24328-af42-408a-9f3b-dd23b7108fb4 Gowen, Michael Michael.Gowen@ukpowernetworks.co.uk, S1 Installed 39c23e0b-0098-4eb4-9613-232a005eee82 1204947b-45e4-44db-8276-c4cfa73c676d FD-3wqZZo23KVTG 1 ada75844-368f-445e-9e8c-21c57c8bae6e Penson, Mark
Mark.Penson@ukpowernetworks.co.uk, S1 Installed 39c23e0b-0098-4eb4-9613-232a005eee82 2747782c-fdd7-4b76-b983-8e5694021a60 FD-4MGi6Q4VYGMb 1 d61099b3-f4d3-4e19-bcb8-1481d098b848 Capp, Adrian Adrian.Capp@ukpowernetworks.co.uk…}
SessionId :
The Schema property being an array of objects (I believe), means I can call a specific index, then retrieve a property by doing something like $Object.Schema[0].Column which would give me "AppInstallState". This can obviously be used to get various other column names, and their type (string, boolean, datetime etc).
The issue I have, is the actual list of devices and associated data is stored in the Values property, which appears to be an array of strings? Therefore, it seems a bit more difficult to recall specific devices. I have no ability to lookup one of the Values based on the devicename (e.g $Values | Where-Object {$_.DeviceName -eq ''}
How can I take the schema referenced from the Schema property, combine it with the Values property, to give me an array of objects where the property names are the column names from the schema, and the values are the strings from the Values property.
Would I just have to manually iterate through all the values, and manually build a PSCustomObject?
EDIT: Details of Invoke-RestMethod commands:
$body = @{
select = @(
"DeviceName"
"UserPrincipalName"
"InstallState"
"DeviceId"
"ErrorCode"
"UserName"
"UserId"
"ApplicationId"
"AppInstallState"
)
skip = 0
top = 50
filter = "(ApplicationId eq '$ApplicationID')"
orderBy = @(
)
}
$body = $body | ConvertTo-Json
$GraphAPIResource = "https://graph.microsoft.com/$graphApiVersion/deviceManagement/reports/getDeviceInstallStatusReport"
$GraphAPIReturn = Invoke-RestMethod -Headers @{Authorization = "Bearer $($accesstoken)"} -Uri $GraphAPIResource -Method Post -Body $body -ContentType "application/json"
Using the data from your previous question there are two properties that define the data:
Schema - contains a list of column namesValues - contains a jagged array where each child item is an array of property values that run in parallel to the Schema column definitionsThis format is presumably to reduce the amount of bytes needed to be sent when downloading a report with a large number of items in Values as it can contain a compact json array of values for each item rather than a json object that repeats column names in each entry.
Here's the sample data from your other question:
$json = @"
{"TotalRowCount":917,"Schema":[{"Column":"AppInstallState","PropertyType":"String"},{"Column":"AppInstallState_loc","PropertyType":"String"},{"Column":"AppInstallStateDetails","PropertyType":"String"},{"Column":"AppInstallStateDetails_loc","PropertyType":"String"},{"Column":"ApplicationId","PropertyType":"String"},{"Column":"AppVersion","PropertyType":"String"},{"Column":"AssignmentFilterIdsExist","PropertyType":"SByte"},{"Column":"AssignmentFilterIdsList","PropertyType":"String"},{"Column":"DeviceId","PropertyType":"String"},{"Column":"DeviceName","PropertyType":"String"},{"Column":"ErrorCode","PropertyType":"Int32"},{"Column":"HexErrorCode","PropertyType":"String"},{"Column":"InstallState","PropertyType":"Int32"},{"Column":"InstallStateDetail","PropertyType":"Int32"},{"Column":"LastModifiedDateTime","PropertyType":"DateTime"},{"Column":"Platform","PropertyType":"String"},{"Column":"UserId","PropertyType":"String"},{"Column":"UserName","PropertyType":"String"},{"Column":"UserPrincipalName","PropertyType":"String"}],"Values":[["S1","Installed","E0","","39c23e0b-0098-4eb4-9613-232a005eee82","6.96.170",false,"","5010bc04-d22f-46ba-94bb-4bd3213ad13f","CPC-james-NSYIT","","",1,0,"2023-10-10T10:13:30","Windows 10.0.19045.3570","82633388-108c-4ea9-842e-ff9a849f5159","[redacted]","[redacted]"],["S1","Installed","E0","","39c23e0b-0098-4eb4-9613-232a005eee82","6.96.170",false,"","f8ec0b7a-044a-4098-be27-b680072a3b83","FD-2do7AN5hEMv2","","",1,0,"2023-10-12T10:38:23","Windows 10.0.22621.2428","b7c24328-af42-408a-9f3b-dd23b7108fb4","[redacted]","[redacted]"]],"SessionId":""}
"@;
$data = $json | ConvertFrom-Json
$data | fl *
# TotalRowCount : 917
# Schema : {@{Column=AppInstallState; PropertyType=String}, #
# @{Column=AppInstallState_loc;
# PropertyType=String}, @{Column=AppInstallStateDetails; PropertyType=String},
# @{Column=AppInstallStateDetails_loc; PropertyType=String}…}
# Values : {S1 Installed E0 39c23e0b-0098-4eb4-9613-232a005eee82 6.96.170 False
# 5010bc04-d22f-46ba-94bb-4bd3213ad13f CPC-james-NSYIT 1 0 10/10/2023 10:13:30
# Windows 10.0.19045.3570 82633388-108c-4ea9-842e-ff9a849f5159 [redacted]
# [redacted], S1 Installed E0
# 39c23e0b-0098-4eb4-9613-232a005eee82 6.96.170 False
# f8ec0b7a-044a-4098-be27-b680072a3b83 FD-2do7AN5hEMv2 1 0 12/10/2023 10:38:23
# Windows 10.0.22621.2428 b7c24328-af42-408a-9f3b-dd23b7108fb4 [redacted]
# [redacted]}
# SessionId :
and to convert it into a simpler representation you can do this:
$objects = $data.Values | foreach-object `
-Begin {
$propertyNames = @($data.Schema.Column)
} `
-Process {
$properties = [ordered] @{};
for( $i = 0; $i -lt $data.Schema.Length; $i++ )
{
$properties[$propertyNames[$i]] = $_[$i];
}
new-object PSCustomObject -Property $properties
}
This basically loops over each item in Values and pairs the items in the child array with the column name in the Schema, and then builds a PSCustomObject for each one:
$objects | fl *
AssignmentFilterIdsExist : False
InstallState : 1
AppInstallStateDetails : E0
AssignmentFilterIdsList :
LastModifiedDateTime : 10/10/2023 10:13:30
UserId : 82633388-108c-4ea9-842e-ff9a849f5159
AppInstallStateDetails_loc :
UserName : [redacted]
AppInstallState : S1
DeviceId : 5010bc04-d22f-46ba-94bb-4bd3213ad13f
Platform : Windows 10.0.19045.3570
AppInstallState_loc : Installed
DeviceName : CPC-james-NSYIT
ErrorCode :
InstallStateDetail : 0
AppVersion : 6.96.170
UserPrincipalName : [redacted]
ApplicationId : 39c23e0b-0098-4eb4-9613-232a005eee82
HexErrorCode :
AssignmentFilterIdsExist : False
InstallState : 1
AppInstallStateDetails : E0
AssignmentFilterIdsList :
LastModifiedDateTime : 12/10/2023 10:38:23
UserId : b7c24328-af42-408a-9f3b-dd23b7108fb4
AppInstallStateDetails_loc :
UserName : [redacted]
AppInstallState : S1
DeviceId : f8ec0b7a-044a-4098-be27-b680072a3b83
Platform : Windows 10.0.22621.2428
AppInstallState_loc : Installed
DeviceName : FD-2do7AN5hEMv2
ErrorCode :
InstallStateDetail : 0
AppVersion : 6.96.170
UserPrincipalName : [redacted]
ApplicationId : 39c23e0b-0098-4eb4-9613-232a005eee82
HexErrorCode :