smsagent.blog
  • docs.smsagent.blog
  • Custom Reporting in Microsoft Intune
    • Delivery Optimization Report
    • Windows Update for Business Custom Reporting
      • Power BI Report Walkthrough
      • Known issues / limitations
      • Change log
      • Deploy the solution
        • Create Azure Resources
        • Configure Azure Resources
        • Deploy the client-side script
        • Deploy the Azure automation runbooks
        • Configure the Power BI report
      • Adding additional language support
      • Table schema reference
    • Automating Data Exports from Microsoft Graph
      • Azure Automation account
        • Create / configure an Azure automation account
        • Grant API permissions
        • Create an Azure automation runbook
      • Azure Storage account
      • Automate Data Export to Azure Storage Account
      • Automate Data Export to Azure Monitor Logs
      • Creating / Troubleshooting Runbooks
      • Power BI
        • Connect Power BI to an Azure storage account data source
        • Connect Power BI to an Azure log analytics workspace as a data source
    • Managed Devices Report
      • Create / configure an Azure automation account
      • Grant API permissions
      • Create / configure an Azure storage account
      • Create an Azure automation runbook
      • Create a PowerBI report
      • MEM Managed Device Report template
      • Bonus! Unhealthy MEMCM Clients email report
    • Intune Assignments Report
      • Create / configure an Azure automation account
      • Grant API permissions
      • Create / configure an Azure storage account
      • Create an Azure automation runbook
      • Create a Power BI report
      • Change log
    • Patch My PC Report
      • A look at the Power BI reports
      • Change log
      • Video guides
      • Things to know
      • Create / configure an Azure automation account
      • Grant API permissions
      • Create / configure an Azure storage account
      • Create an Azure automation runbook
      • Create the Power BI report
      • Feedback
    • Windows 11 Hardware Readiness Report
    • Gathering Custom Inventory with Intune
      • Set up the Azure Resources
      • Create a Proactive remediations script package
      • Create a runbook
  • PowerShell Scripts Online Help
    • Get-AzSubscriptionActivityLog
  • Azure Solutions
    • Automated Azure Table Storage Backups
      • Change log
      • Deploy the solution
        • Create the Azure resources
        • Set the backup schedule
        • Add storage tables to the backup
        • Add role assignments to the storage account/s
        • Create a lifecycle management rule
      • Run a manual backup
      • Restore a backup
Powered by GitBook
On this page
  • Get your Proactive remediations script package GUID
  • Create a runbook (sending to Azure storage account)
  • Create a runbook (sending to Log Analytics workspace)
  • Schedule the runbook

Was this helpful?

  1. Custom Reporting in Microsoft Intune
  2. Gathering Custom Inventory with Intune

Create a runbook

PreviousCreate a Proactive remediations script packageNextGet-AzSubscriptionActivityLog

Last updated 3 years ago

Was this helpful?

Create an Azure automation runbook that will export your inventory data from Microsoft Graph.

Get your Proactive remediations script package GUID

For the automation runbook, we'll need to know the GUID of the script package we just created. An easy way to do this is to use the Developer Tools in Microsoft Edge.

In the MEM portal, navigate to Proactive remediations, but before you click on the script package you created, press F12 to open the DevTools. Click on the Network tab at the top.

Then click to open the script package and you'll see the URL/s that the portal is calling. You'll see one with the name getRemediationHistory, for example - right-click on that and copy the link address.

You can then paste it into notepad, for example, and you'll have the GUID that you need in the URL. Make a note of this GUID.

Create a runbook (sending to Azure storage account)

Download the following runbooks script.

In your Azure automation account, navigate to Runbooks and Import a runbook. Browse to and import the runbook script, select the Runbook type PowerShell and add a description.

In the runbook script you'll need to add some parameter values at the top:

  • $ResourceGroup. This is the name of the resource group that your storage account belongs to.

  • $StorageAccount. This is the name of your storage account.

  • $Container. This is the name of the container in the storage account where you'll export your data to (must exist)

  • $ProactiveRemediationsScriptGUID. This is the GUID for your PR script package that you obtained earlier.

The rest of the script can remain unchanged.

Publish the runbook, then start it. After execution is complete, you should see the CSV file created in your storage account.

On the Edit tab of the blob, you can see your exported data.

For completeness, here's the same data imported into Power BI.

Create a runbook (sending to Log Analytics workspace)

First you'll need to add the WorkspaceID and PrimaryKey from the Log Analytics workspace as encrypted variables in the Azure automation account.

In your LA workspace, go to Agents management and copy the Workspace ID and Primary key.

In the automation account, go to Variables and Add a variable.

Create the following variables as strings and paste in the copied values. Select the Encryption option.

  • WorkspaceID

  • PrimaryKey

Now download the following runbook script.

In your Azure automation account, navigate to Runbooks and Import a runbook. Browse to and import the runbook script, select the Runbook type PowerShell and add a description.

In the runbook script you'll need to add some parameter values at the top:

  • $ProactiveRemediationsScriptGUID. This is the GUID for your PR script package that you obtained earlier.

  • $LogName. This is the name of the custom log you'll send data to in the LA workspace. It doesn't need to already exist.

As long as your automation account variables are named the same as in the script, you don't need to change those.

The rest of the script can remain unchanged.

Publish the runbook, then start it. After execution is complete, you can query the data in the custom log from the LA workspace. Bear in mind that for the first run it can take some minutes before the data is ingested and available in the workspace.

The custom log name will be appended with "_CL" and only non-null values will be ingested and available to report on.

Schedule the runbook

Schedule the Runbook to execute at the desired intervals to keep the data updated.

  • In the Azure portal, in the automation account, open the Runbook you created

  • Click Link to schedule

  • In the Schedule section, select an existing schedule or create a new one

https://github.com/SMSAgentSoftware/MEM/blob/main/%5BReporting%5D%20SoftwareUpdates/Export-MSGraphInventoryData-SoftwareUpdates.ps1
###################################################################################

## Azure automation runbook PowerShell script to export inventory data from      ##

## Proactive remediations in Microsoft Intune and dump it to Azure Blob storage  ##

## where it can be used as a datasource for Power BI.                            ##

###################################################################################



## Module Requirements ##

# Az.Accounts

# Az.Storage





# Set some variables

$ResourceGroup = "<my-resource-group>" # Reource group that hosts the storage account

$StorageAccount = "<my-storage-account>" # Storage account name

$Container = "intune-custom-inventory" # Container name

$ProactiveRemediationsScriptGUID = "<GUID>" # GUID of the Proactive remediations script package



$ProgressPreference = 'SilentlyContinue'

$VerbosePreference = 'Continue'

$TempFileName = "SoftwareUpdatesTemp"

$FinalFileName = "SoftwareUpdatesData"

$Destination = "$env:Temp"







####################

## AUTHENTICATION ##

####################

$url = $env:IDENTITY_ENDPOINT  

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 

$headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 

$headers.Add("Metadata", "True") 

$body = @{resource='https://graph.microsoft.com/' } 

$script:accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token

$script:authHeader = @{

    'Authorization' = "Bearer $accessToken"

}



Connect-AzAccount -Identity







###############

## FUNCTIONS ##

###############

# Function to make a web reqeust to Graph with error handling

Function script:Invoke-LocalGraphRequest {

    Param ($URL,$Headers,$Method,$Body,$ContentType)

    try {

        If ($Method -eq "Post")

        {

            $WebRequest = Invoke-WebRequest -Uri $URL -Method $Method -Headers $Headers -Body $Body -ContentType $ContentType -UseBasicParsing

        }

        else 

        {

            $WebRequest = Invoke-WebRequest -Uri $URL -Method $Method -Headers $Headers -UseBasicParsing

        }     

    }

    catch {

        $WebRequest = $_.Exception.Response

    }

    Return $WebRequest

}



# Function to get an export job from Graph

Function Get-MSGraphExportJob {

    Param($ReportName,$Filter,$FileName,$Destination)



    $bodyHash = [ordered]@{

        reportName = $ReportName

        filter = $Filter

    }

    $bodyJson = $bodyHash | ConvertTo-Json -Depth 3



    $URL = "https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs"

    $Headers = @{'Authorization'="Bearer " + $accessToken; 'Accept'="application/json"}

    $GraphRequest = Invoke-LocalGraphRequest -URL $URL -Headers $Headers -Method POST -Body $bodyJson -ContentType "application/json"

    If ($GraphRequest.StatusCode -ne 201)

    {

        Return $GraphRequest

    }



    $Id = ($GraphRequest.Content | ConvertFrom-Json).Id

    do {

        Start-Sleep -Seconds 5

        $URL = "https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('$Id')"

        $Headers = @{'Authorization'="Bearer " + $accessToken; 'Accept'="application/json"}

        $WebResponse = Invoke-LocalGraphRequest -URL $URL -Headers $Headers -Method GET

        $ReponseJson = $WebResponse.Content | ConvertFrom-Json

        $Status = $ReponseJson.status

    }

    Until ($Status -eq "Completed")



    $DownloadUrl = $ReponseJson.url   

    try {

        $DownloadRequest = Invoke-WebRequest -Uri $DownloadUrl -OutFile "$Destination\$FileName.zip" -UseBasicParsing -PassThru

    }

    catch {

        $DownloadRequest = $_.Exception.Response

    }

    Return $DownloadRequest



}



# Function to remove duplicate entries per deviceId retaining the latest entry only, for an export job

Function Remove-MSGraphExportJobDuplicates {

    Param([System.Collections.ArrayList]$Collection)



    # Filter out duplicate DeviceIds into an array

    [array]$DeviceIDs = $Collection.DeviceId

    $NonDuplicatesHash = @{}

    $DuplicatesArray = [System.Collections.ArrayList]::new()

    foreach ($DeviceID in $DeviceIDs)

    {

        try {

            $NonDuplicatesHash.Add($DeviceID,0)

        }

        catch [System.Management.Automation.MethodInvocationException] {

            [void]$DuplicatesArray.Add($DeviceID)

        }

    }



    # Remove all but the latest (ModifiedTime) entry in the collection for each duplicate

    $DuplicatesArray = $DuplicatesArray | Select -Unique	

    foreach ($Duplicate in $DuplicatesArray)

    {

        $Array = $Collection.Where({$_.DeviceId -eq $Duplicate})

        #$Latest = $Array | Sort ModifiedTime -Descending | Select -First 1

        $Others = $Array | Sort ModifiedTime -Descending | Select -Skip 1

        foreach ($item in $Others)

        {

            $Collection.Remove($item)

        }

    }



    Return $Collection



}







#################

## MAIN SCRIPT ##

#################



# Get an export job from Graph from Proactive Remediations (DO data) and handle errors

$Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $TempFileName -Destination $Destination

If ($Report.GetType().BaseType -eq [System.Net.WebResponse] -or $Report.GetType().Name -eq "WebResponseObject")

{

    If ($Report.GetType().Name -eq "WebResponseObject")

    {

        If ($Report.StatusCode -eq 504)

        {

            # Server timeout encountered, lets try again a couple of times

            Write-Warning -Message "Http 504 (gateway timeout) encountered while getting Graph data. Retrying up to 3 times."

            [int]$RetryAttempts = 0

            do {

                $RetryAttempts ++ 

                Start-Sleep -Seconds 5

                $Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $FileName -Destination $Destination

            }

            until ($RetryAttempts -gt 3 -or $Report.StatusCode -eq 200)

        }

        ElseIf ($Report.StatusCode -ne 200)

        {

            throw "Http error encountered from Graph API. Status code: $($Report.StatusCode). Status description: $($Report.StatusDescription)."

            Exit 1

        }

    }

    else 

    {

        If ($Report.StatusCode.value__ -eq 504)

        {

            # Server timeout encountered, lets try again a couple of times

            Write-Warning -Message "Http 504 (gateway timeout) encountered while getting Graph data. Retrying up to 3 times."

            [int]$RetryAttempts = 0

            do {

                $RetryAttempts ++ 

                Start-Sleep -Seconds 5

                $Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $FileName -Destination $Destination

            }

            until ($RetryAttempts -gt 3 -or $Report.GetType().BaseType -ne [System.Net.WebResponse])

        }

        If ($Report.GetType().BaseType -eq [System.Net.WebResponse])

        {

            throw "Http error encountered from Graph API. Status code: $($Report.StatusCode.value__). Status description: $($Report.StatusDescription)."

            Exit 1

        }

    }

}



# Extract the CSV file from the exportJob and import it

Start-Sleep -Seconds 5

Unblock-File -Path "$Destination\$TempFileName.zip"

$CsvFile = (Expand-Archive -Path "$Destination\$TempFileName.zip" -DestinationPath $Destination -Force -Verbose) 4>&1

$CsvFileName = $CsvFile[-1].ToString().Split('\')[-1].Replace("'.",'')

$File = Get-Childitem -Path $Destination\$CsvFileName -File

Rename-Item -Path $File.FullName -NewName "$TempFileName.csv" -Force

[array]$ImportedResults = Import-Csv $Destination\$TempFileName.csv -UseCulture

[System.Collections.ArrayList]$ImportedResults = $ImportedResults | 

    where {$_.PreRemediationDetectionScriptOutput -ne "" -and $_.DetectionStatus -eq 3 } | 

    Select -Property DeviceId,ModifiedTime,PreRemediationDetectionScriptOutput,DeviceName



# Filter out duplicate entries keep only the most recent per device

# !! This can take some time and processing power on a large data set !!

$FilteredResults = Remove-MSGraphExportJobDuplicates -Collection $ImportedResults | Sort DeviceName



# Create a datatable to hold the results

# Add the column names for the data you are collecting from PR

$DataTable = [System.Data.DataTable]::new()

"DeviceId","DeviceName","SU_ScheduledRebootTime","SU_RebootRequired","SU_EngageReminderLastShownTime","SU_PendingRebootStartTime" | foreach {

    [void]$DataTable.Columns.Add($_)

}

Foreach ($Item in $FilteredResults)

{

    $Data = $Item.PreRemediationDetectionScriptOutput | ConvertFrom-json

    $SU_ScheduledRebootTime = $Data.SU_ScheduledRebootTime

    $SU_RebootRequired = $Data.SU_RebootRequired

    $SU_EngageReminderLastShownTime = $Data.SU_EngageReminderLastShownTime

    $SU_PendingRebootStartTime = $Data.SU_PendingRebootStartTime

    [void]$DataTable.Rows.Add(

        $Item.DeviceId,

        $Item.DeviceName,

        $SU_ScheduledRebootTime,

        $SU_RebootRequired,

        $SU_EngageReminderLastShownTime,

        $SU_PendingRebootStartTime

    )

}



$DataTable.DefaultView.Sort = "DeviceName"

$DataTable = $DataTable.DefaultView.ToTable($true)



# Export the datatable to CSV and upload to Azure storage

$DataTable | Export-Csv -Path $Destination\$FinalFileName.csv -NoTypeInformation -Force

$StorageAccount = Get-AzStorageAccount -Name $StorageAccount -ResourceGroupName $ResourceGroup

try {

    Write-Verbose -Verbose "Uploading $FinalFileName.csv to Azure storage container $Container"

    $null = Set-AzStorageBlobContent -File "$Destination\$FinalFileName.csv" -Container $Container -Blob $FinalFileName.csv -Context $StorageAccount.Context -Force -ErrorAction Stop

}

catch {

    Write-Error -Exception $_ -Message "Failed to upload $FinalFileName.csv to blob storage"

} 

https://github.com/SMSAgentSoftware/MEM/blob/main/%5BReporting%5D%20SoftwareUpdates/Export-MSGraphInventoryData-SoftwareUpdates_LA.ps1
##############################################################################################

## Azure automation runbook PowerShell script to export inventory data from                 ##

## Proactive remediations in Microsoft Intune and send it to an Azure Log Analytics         ##

## workspace where it can be used as a datasource for Power BI or an Azure Monitor workbook ##

##############################################################################################



## Module Requirements ##

# None



# Set some variables

$ProactiveRemediationsScriptGUID = "<GUID>" # GUID of the Proactive remediations script package

$WorkspaceID = Get-AutomationVariable -Name "WorkspaceID" # Saved as an encrypted variable in the automation account 

$PrimaryKey = Get-AutomationVariable -Name "PrimaryKey" # Saved as an encrypted variable in the automation account 

$LogName = "INV_SoftwareUpdates" # The name of the custom log in the LA workspace to send the data to



$ProgressPreference = 'SilentlyContinue'

$VerbosePreference = 'Continue'

$TempFileName = "SoftwareUpdatesTemp"

$Destination = "$env:Temp"







####################

## AUTHENTICATION ##

####################

$url = $env:IDENTITY_ENDPOINT  

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 

$headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 

$headers.Add("Metadata", "True") 

$body = @{resource='https://graph.microsoft.com/' } 

$script:accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token

$script:authHeader = @{

    'Authorization' = "Bearer $accessToken"

}







###############

## FUNCTIONS ##

###############

# Function to make a web reqeust to Graph with error handling

Function script:Invoke-LocalGraphRequest {

    Param ($URL,$Headers,$Method,$Body,$ContentType)

    try {

        If ($Method -eq "Post")

        {

            $WebRequest = Invoke-WebRequest -Uri $URL -Method $Method -Headers $Headers -Body $Body -ContentType $ContentType -UseBasicParsing

        }

        else 

        {

            $WebRequest = Invoke-WebRequest -Uri $URL -Method $Method -Headers $Headers -UseBasicParsing

        }     

    }

    catch {

        $WebRequest = $_.Exception.Response

    }

    Return $WebRequest

}



# Function to get an export job from Graph

Function Get-MSGraphExportJob {

    Param($ReportName,$Filter,$FileName,$Destination)



    $bodyHash = [ordered]@{

        reportName = $ReportName

        filter = $Filter

    }

    $bodyJson = $bodyHash | ConvertTo-Json -Depth 3



    $URL = "https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs"

    $Headers = @{'Authorization'="Bearer " + $accessToken; 'Accept'="application/json"}

    $GraphRequest = Invoke-LocalGraphRequest -URL $URL -Headers $Headers -Method POST -Body $bodyJson -ContentType "application/json"

    If ($GraphRequest.StatusCode -ne 201)

    {

        Return $GraphRequest

    }



    $Id = ($GraphRequest.Content | ConvertFrom-Json).Id

    do {

        Start-Sleep -Seconds 5

        $URL = "https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('$Id')"

        $Headers = @{'Authorization'="Bearer " + $accessToken; 'Accept'="application/json"}

        $WebResponse = Invoke-LocalGraphRequest -URL $URL -Headers $Headers -Method GET

        $ReponseJson = $WebResponse.Content | ConvertFrom-Json

        $Status = $ReponseJson.status

    }

    Until ($Status -eq "Completed")



    $DownloadUrl = $ReponseJson.url   

    try {

        $DownloadRequest = Invoke-WebRequest -Uri $DownloadUrl -OutFile "$Destination\$FileName.zip" -UseBasicParsing -PassThru

    }

    catch {

        $DownloadRequest = $_.Exception.Response

    }

    Return $DownloadRequest



}



# Function to remove duplicate entries per deviceId retaining the latest entry only, for an export job

Function Remove-MSGraphExportJobDuplicates {

    Param([System.Collections.ArrayList]$Collection)



    # Filter out duplicate DeviceIds into an array

    [array]$DeviceIDs = $Collection.DeviceId

    $NonDuplicatesHash = @{}

    $DuplicatesArray = [System.Collections.ArrayList]::new()

    foreach ($DeviceID in $DeviceIDs)

    {

        try {

            $NonDuplicatesHash.Add($DeviceID,0)

        }

        catch [System.Management.Automation.MethodInvocationException] {

            [void]$DuplicatesArray.Add($DeviceID)

        }

    }



    # Remove all but the latest (ModifiedTime) entry in the collection for each duplicate

    $DuplicatesArray = $DuplicatesArray | Select -Unique	

    foreach ($Duplicate in $DuplicatesArray)

    {

        $Array = $Collection.Where({$_.DeviceId -eq $Duplicate})

        #$Latest = $Array | Sort ModifiedTime -Descending | Select -First 1

        $Others = $Array | Sort ModifiedTime -Descending | Select -Skip 1

        foreach ($item in $Others)

        {

            $Collection.Remove($item)

        }

    }



    Return $Collection



}



# Create the function to create the authorization signature

# ref https://docs.microsoft.com/en-us/azure/azure-monitor/logs/data-collector-api

Function Build-Signature ($customerId, $sharedKey, $date, $contentLength, $method, $contentType, $resource)

{

    $xHeaders = "x-ms-date:" + $date

    $stringToHash = $method + "`n" + $contentLength + "`n" + $contentType + "`n" + $xHeaders + "`n" + $resource



    $bytesToHash = [Text.Encoding]::UTF8.GetBytes($stringToHash)

    $keyBytes = [Convert]::FromBase64String($sharedKey)



    $sha256 = New-Object System.Security.Cryptography.HMACSHA256

    $sha256.Key = $keyBytes

    $calculatedHash = $sha256.ComputeHash($bytesToHash)

    $encodedHash = [Convert]::ToBase64String($calculatedHash)

    $authorization = 'SharedKey {0}:{1}' -f $customerId,$encodedHash

    return $authorization

}



# Create the function to create and post the request

# ref https://docs.microsoft.com/en-us/azure/azure-monitor/logs/data-collector-api

Function Post-LogAnalyticsData($customerId, $sharedKey, $body, $logType)

{

    $method = "POST"

    $contentType = "application/json"

    $resource = "/api/logs"

    $rfc1123date = [DateTime]::UtcNow.ToString("r")

    $contentLength = $body.Length

    $TimeStampField = ""

    $signature = Build-Signature `

        -customerId $customerId `

        -sharedKey $sharedKey `

        -date $rfc1123date `

        -contentLength $contentLength `

        -method $method `

        -contentType $contentType `

        -resource $resource

    $uri = "https://" + $customerId + ".ods.opinsights.azure.com" + $resource + "?api-version=2016-04-01"



    $headers = @{

        "Authorization" = $signature;

        "Log-Type" = $logType;

        "x-ms-date" = $rfc1123date;

        "time-generated-field" = $TimeStampField;

    }



    $response = Invoke-WebRequest -Uri $uri -Method $method -ContentType $contentType -Headers $headers -Body $body -UseBasicParsing

    return $response.StatusCode

}





#################

## MAIN SCRIPT ##

#################



# Get an export job from Graph from Proactive Remediations (DO data) and handle errors

$Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $TempFileName -Destination $Destination

If ($Report.GetType().BaseType -eq [System.Net.WebResponse] -or $Report.GetType().Name -eq "WebResponseObject")

{

    If ($Report.GetType().Name -eq "WebResponseObject")

    {

        If ($Report.StatusCode -eq 504)

        {

            # Server timeout encountered, lets try again a couple of times

            Write-Warning -Message "Http 504 (gateway timeout) encountered while getting Graph data. Retrying up to 3 times."

            [int]$RetryAttempts = 0

            do {

                $RetryAttempts ++ 

                Start-Sleep -Seconds 5

                $Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $FileName -Destination $Destination

            }

            until ($RetryAttempts -gt 3 -or $Report.StatusCode -eq 200)

        }

        ElseIf ($Report.StatusCode -ne 200)

        {

            throw "Http error encountered from Graph API. Status code: $($Report.StatusCode). Status description: $($Report.StatusDescription)."

            Exit 1

        }

    }

    else 

    {

        If ($Report.StatusCode.value__ -eq 504)

        {

            # Server timeout encountered, lets try again a couple of times

            Write-Warning -Message "Http 504 (gateway timeout) encountered while getting Graph data. Retrying up to 3 times."

            [int]$RetryAttempts = 0

            do {

                $RetryAttempts ++ 

                Start-Sleep -Seconds 5

                $Report = Get-MSGraphExportJob -ReportName DeviceRunStatesByProactiveRemediation -Filter "PolicyId eq '$ProactiveRemediationsScriptGUID'" -FileName $FileName -Destination $Destination

            }

            until ($RetryAttempts -gt 3 -or $Report.GetType().BaseType -ne [System.Net.WebResponse])

        }

        If ($Report.GetType().BaseType -eq [System.Net.WebResponse])

        {

            throw "Http error encountered from Graph API. Status code: $($Report.StatusCode.value__). Status description: $($Report.StatusDescription)."

            Exit 1

        }

    }

}



# Extract the CSV file from the exportJob and import it

Start-Sleep -Seconds 5

Unblock-File -Path "$Destination\$TempFileName.zip"

$CsvFile = (Expand-Archive -Path "$Destination\$TempFileName.zip" -DestinationPath $Destination -Force -Verbose) 4>&1

$CsvFileName = $CsvFile[-1].ToString().Split('\')[-1].Replace("'.",'')

$File = Get-Childitem -Path $Destination\$CsvFileName -File

Rename-Item -Path $File.FullName -NewName "$TempFileName.csv" -Force

[array]$ImportedResults = Import-Csv $Destination\$TempFileName.csv -UseCulture

[System.Collections.ArrayList]$ImportedResults = $ImportedResults | 

    where {$_.PreRemediationDetectionScriptOutput -ne "" -and $_.DetectionStatus -eq 3 } | 

    Select -Property DeviceId,ModifiedTime,PreRemediationDetectionScriptOutput,DeviceName



# Filter out duplicate entries keep only the most recent per device

# !! This can take some time and processing power on a large data set !!

$FilteredResults = Remove-MSGraphExportJobDuplicates -Collection $ImportedResults | Sort DeviceName



# Create a datatable to hold the results

# Add the column names for the data you are collecting from PR

$DataTable = [System.Data.DataTable]::new()

"DeviceId","DeviceName","SU_ScheduledRebootTime","SU_RebootRequired","SU_EngageReminderLastShownTime","SU_PendingRebootStartTime" | foreach {

    [void]$DataTable.Columns.Add($_)

}

Foreach ($Item in $FilteredResults)

{

    $Data = $Item.PreRemediationDetectionScriptOutput | ConvertFrom-json

    $SU_ScheduledRebootTime = $Data.SU_ScheduledRebootTime

    $SU_RebootRequired = $Data.SU_RebootRequired

    $SU_EngageReminderLastShownTime = $Data.SU_EngageReminderLastShownTime

    $SU_PendingRebootStartTime = $Data.SU_PendingRebootStartTime

    [void]$DataTable.Rows.Add(

        $Item.DeviceId,

        $Item.DeviceName,

        $SU_ScheduledRebootTime,

        $SU_RebootRequired,

        $SU_EngageReminderLastShownTime,

        $SU_PendingRebootStartTime

    )

}



$DataTable.DefaultView.Sort = "DeviceName"

$DataTable = $DataTable.DefaultView.ToTable($true)





# Send the data to the LA workspace

$Json = $DataTable | Select -Property @($DataTable.Columns.ColumnName) | ConvertTo-Json

Post-LogAnalyticsData -customerId $WorkspaceID -sharedKey $PrimaryKey -body ([System.Text.Encoding]::UTF8.GetBytes($json)) -logType $LogName