Exporting Data from Endpoint Analytics Proactive Remediations

How to use Graph API to retrieve results from an Endpoint Analytics

Endpoint Analytics [LINK] is a great new feature in Microsoft Endpoint Manager. Cloud only Endpoint Manager clients can be actively managed using Proactive Remediation scripts.

This article is about a mechanism to export the reported data from Endpoint Analytics using Graph API.

My Proactive Remediation Solution

Recently I encountered an issue where the Endorsement Key was not downloaded to Intel Trusted Platform Modules during Autopilot device enrollment. The project faced a situation where a critical security feature did not work on an unknown number of devices. We knew some devices had failed to correctly download the Endorsement Key but not which devices.

I looked at a number of options including a custom solution using Azure Function Apps but settled on a simpler solution using ProActive Remediation scripts. The solution queried the status of the Endorsement Key and reported a pass/fail to Endpoint Analytics.

You can download the script code from my GitHub repository [LINK]

The Problem with Device Status

The Proactive Remediation worked perfectly to identify the the failed devices. however, I quickly ran into an issue with the Endpoint Manager GUI once the number of reported devices exceeded about 40 devices.

The GUI would display a subset of the device status results but clicking on Load More at the bottom of the report wiped the display.

So we knew a bunch of devices had failed but could not obtain a complete list of devices.

Graph API for Reporting

The solution to effective reporting was leveraging Graph API to query the Proactive Remediate device status then export to a csv file. The complete code is shown below and is also available from GitHub [LINK]

Param(
    [Parameter(Mandatory)]
    [string]$OutputFile
)
Update-MSGraphEnvironment -SchemaVersion beta

Connect-MSGraph

#Create an array for the output
$TPMEKOutput = New-Object System.Collections.ArrayList

#Get the details of the TPM report
Write-Host "Retrieving TPM Report Device States"
$AllResultsRetrieved = $False
$ResultsRetrieved = 0

do {
    If ($ResultsRetrieved -eq 0) {
        #Set the 'TPM EK URL for the first run
        $TPMInvokeURL = 'deviceManagement/deviceHealthScripts/<guid>/deviceRunStates?'
    }
    #Retrieve the results
    $TPMEKResults = Invoke-MSGraphRequest -HttpMethod GET -Url $TPMInvokeURL

    #Process the returned data
    $TPMEKResultData = $TPMEKResults.Value
    Foreach ($TPMResult in $TPMEKResultData) {
        If (($TPMResult.detectionState -eq "fail") -and ($TPMResult.remediationState -eq "remediationFailed")) {
            $SerialNoIndex = $TPMResult.preRemediationDetectionScriptOutput.indexof("Number") + 7
            $SerialNo = $TPMResult.preRemediationDetectionScriptOutput.Substring($SerialNoIndex,($TPMResult.preRemediationDetectionScriptOutput.Length-$SerialNoIndex))
            $TempTPMOutput = New-Object -TypeName PSObject -Property @{
                'detectionState' = $TPMResult.detectionState
                'remediationState' = $TPMResult.remediationState
                'lastStateUpdateDateTime' = $TPMResult.lastStateUpdateDateTime
                'lastSyncDateTime' = $TPMResult.lastSyncDateTime
                'preRemediationDetectionScriptOutput' = $TPMResult.preRemediationDetectionScriptOutput
                'SerialNo' = $SerialNo
            }
            $TPMEKOutput.Add($TempTPMOutput) | out-null
        }
    }

    If ($TPMEKResults."@odata.count" -eq 1000) {
        Write-Host "Retrieving next 1000 results"
        $ResultsRetrieved = $ResultsRetrieved + $TPMEKResults."@odata.count"
        $TPMInvokeURL= $TPMEKResults."@odata.nextLink"
    } else {
        $ResultsRetrieved = $ResultsRetrieved + $TPMEKDetails."@odata.count"
        $AllResultsRetrieved = $True
        write-host "Processed $($ResultsRetrieved) device results"
    }

    
} until ($AllResultsRetrieved -eq $True)

Write-Host "Retrieved $($TPMEKOutput.count) Devices with failed TPMs"

$TPMEKOutput | Select-Object SerialNo,detectionState,remediationState,lastStateUpdateDateTime,lastSyncDateTime,preRemediationDetectionScriptOutput | Sort SerialNo | Export-Csv -notypeinformation $OutputFile

Breaking the Script Down

Posting scripts with zero explanation of the script execution is one of my pet hates. The reader learns little if the script code is not obvious or they lack the knowledge to understand. Hopefully this break down of the script flow will help someone else use the same technique for a different problem.

The top few lines specify a parameter that will be used as the output csv file.

Param(
    [Parameter(Mandatory)]
    [string]$OutputFile
)

The next two lines connect to Microsoft Graph

Update-MSGraphEnvironment -SchemaVersion beta

Connect-MSGraph

The output data retrieved from the Graph API Query will be stored in an Array object. This line creates the array object.

#Create an array for the output
$TPMEKOutput = New-Object System.Collections.ArrayList

The main body of the script is a Do Until loop. The Do Until loop allows the loop to continue until a condition is met. In this case, the $AllResultsRetrieved value equaling True

$AllResultsRetrieved = $False
$ResultsRetrieved = 0
do {

} until ($AllResultsRetrieved -eq $True)

Inside the loop, the script queries Graph API using this invoke statement

$TPMEKResults = Invoke-MSGraphRequest -HttpMethod GET -Url $TPMInvokeURL

The invoke statement will retrieve results up-to the maximum page size (1000 results for this Graph API endpoint). However we need to retrieve all the results. We retrieve all the results by using this URL string for the first run of the query.

$TPMInvokeURL = 'deviceManagement/deviceHealthScripts/<guid>/deviceRunStates?'

After the first query succeeds then the query will return an @odata.nextLink field in the query result. @odata.nextLink contains the next URL to obtain the next page. This command sets the Graph API URL for the next loop to the @odata.nextLink URL

$TPMInvokeURL= $TPMEKResults."@odata.nextLink"

An If Else block at the end of the Do Until loop determines whether all of the results have been retrieved with a simple condition. If the result size does not equal the page size then all of the results have been returned.

If ($TPMEKResults."@odata.count" -eq 1000) {
        Write-Host "Retrieving next 1000 results"
        $ResultsRetrieved = $ResultsRetrieved + $TPMEKResults."@odata.count"
        $TPMInvokeURL= $TPMEKResults."@odata.nextLink"
    } else {
        $ResultsRetrieved = $ResultsRetrieved + $TPMEKDetails."@odata.count"
        $AllResultsRetrieved = $True
        write-host "Processed $($ResultsRetrieved) device results"
    }

Inside the results loop. These statements retrieve the results from the Graph API query data and store the data in the results array. Using a PSObject allows the array to contain standard fields that aid the export later.

 #Process the returned data
    $TPMEKResultData = $TPMEKResults.Value
    Foreach ($TPMResult in $TPMEKResultData) {
        If (($TPMResult.detectionState -eq "fail") -and ($TPMResult.remediationState -eq "remediationFailed")) {
            $SerialNoIndex = $TPMResult.preRemediationDetectionScriptOutput.indexof("Number") + 7
            $SerialNo = $TPMResult.preRemediationDetectionScriptOutput.Substring($SerialNoIndex,($TPMResult.preRemediationDetectionScriptOutput.Length-$SerialNoIndex))
            $TempTPMOutput = New-Object -TypeName PSObject -Property @{
                'detectionState' = $TPMResult.detectionState
                'remediationState' = $TPMResult.remediationState
                'lastStateUpdateDateTime' = $TPMResult.lastStateUpdateDateTime
                'lastSyncDateTime' = $TPMResult.lastSyncDateTime
                'preRemediationDetectionScriptOutput' = $TPMResult.preRemediationDetectionScriptOutput
                'SerialNo' = $SerialNo
            }
            $TPMEKOutput.Add($TempTPMOutput) | out-null
        }
    }

Finally the last line formats and exports the data to CSV.

$TPMEKOutput | Select-Object SerialNo,detectionState,remediationState,lastStateUpdateDateTime,lastSyncDateTime,preRemediationDetectionScriptOutput | Sort SerialNo | Export-Csv -notypeinformation $OutputFile

Using a Select-Object statement to select the columns creates a column order in the exported CSV. If you do not perform a select in the pipeline then the CSV output can randomly arrange the columns.

And finally

I hope this solution helps solve a problem for you. Please feel free to re-use my code but if you post a blog article with this code then please attribute where you got the code from.