smsagent.blog
Search…
Create an Azure automation runbook
Here we will create a runbook in the automation account that will retrieve data from Microsoft Graph using the REST API and export the data to the Azure storage account.

Import Modules

We will need a couple of PowerShell modules added to the automation account for the runbook to use.
    In the Azure portal in the automation account, go to the Modules gallery pane
    Search for and import the following modules:
      Az.Accounts
      Az.Storage
      MSAL.PS (only required if using a Run as account)

Create a Runbook

    Click on the Runbooks pane and choose Create a runbook
    Enter a name for the runbook, select PowerShell for the runbook type and click Create
    Copy the PowerShell code below into the runbook and edit it as described below
      If you wish to test the runbook before publishing to make sure it works, use the Test pane
      Alternatively, Publish the runbook, then click Start from the runbook menu. This option will give you the full output of the script.
    When ready, Publish the runbook

Export-MSGraphManagedDeviceData

This PowerShell script can be used as-is as the source for your runbook or as simply as an example that you can modify to your requirements.
To use the provided Managed Devices PowerBI template, use this code as-is because the template is configured to use the fields that this script exports, as well as the container and data file names.
Populate the following parameters at the top of the script:
    $ResourceGroup. This is the name of the resource group that hosts your storage account in Azure
    $StorageAccount. This is the name of the storage account to which you will export data
    $Container. The name of the container to use in the storage account

Managed Identity vs Run as account

The script is configured to run using a managed identity, but code is also included to use a Run as account instead. To use a Run as account, in the Authentication section of the script, simply uncomment the code blocks that start with #Run as account, and comment out the sections that start with #Managed Identity.

What the Runbook does

Authenticate
Get Graph data
Organize the data
Upload the data
First we authenticate to MS Graph and obtain an access token to make our REST API calls with. We also authenticate to Azure AD in order to send data to the storage account.
We query MS Graph to get a list of managed devices in Intune and page through the results to get the full data set.
We then separate out the managed devices by OS creating arrays for iOS, Android and Windows devices.
Not every property returned by MS Graph is useful to us so we have a list of properties to exclude from the results for each OS. We also add a few properties of our own, in some cases simply expanding out nested results into their own fields and in others we add some calculated values of our own, like days since last sync, for example.
Last, we export the results locally into CSV files, one for each OS, then upload these to our storage account. Each CSV file will be placed in its own folder in the storage account container, for example:
    Container > Folder > File
    intune-powerbi > WindowsDevices > WindowsDevices.csv
1
###########################################################################
2
## Azure automation runbook PowerShell script to export device data from ##
3
## Microsoft Intune / Endpoint Manager and dump it to Azure Blob storage ##
4
## where it can be used as a datasource for Power BI. ##
5
###########################################################################
6
7
## Module Requirements ##
8
# Az.Accounts
9
# Az.Storage
10
# MSAL.PS (if using Run as account)
11
12
# Set some variables
13
$ProgressPreference = 'SilentlyContinue'
14
$ResourceGroup = "<myresourcegroupname>" # Reource group that hosts the storage account
15
$StorageAccount = "<mystorageaccountname>" # Storage account name
16
$Container = "intune-powerbi" # Container name
17
18
19
####################
20
## AUTHENTICATION ##
21
####################
22
23
## Get MS Graph access token
24
# Managed Identity
25
$url = $env:IDENTITY_ENDPOINT
26
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
27
$headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER)
28
$headers.Add("Metadata", "True")
29
$body = @{resource='https://graph.microsoft.com/' }
30
$accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token
31
$authHeader = @{
32
'Authorization' = "Bearer $accessToken"
33
}
34
35
# Run as account
36
# Requires MSAL.PS module
37
<#
38
$connectionName = "AzureRunAsConnection"
39
$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName
40
$Cert = Get-Item Cert:\LocalMachine\Root\$($servicePrincipalConnection.CertificateThumbprint)
41
$MsalToken = Get-MsalToken -ClientID $servicePrincipalConnection.ApplicationId -ClientCertificate $Cert -TenantId $servicePrincipalConnection.TenantId -Scopes 'https://graph.microsoft.com/.default'
42
$authHeader = @{
43
'Authorization' = $MsalToken.CreateAuthorizationHeader()
44
}
45
#>
46
47
## Connect to Azure AD
48
# Mmanaged Identity
49
Connect-AzAccount -Identity
50
51
# Run as account
52
#Connect-AzAccount -ServicePrincipal -Tenant $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
53
54
55
#########################
56
## GET DATA FROM GRAPH ##
57
#########################
58
59
$URI = "https://graph.microsoft.com/beta/deviceManagement/manageddevices"
60
$Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing
61
$JsonResponse = $Response.Content | ConvertFrom-Json
62
$DeviceData = $JsonResponse.value
63
If ($JsonResponse.'@odata.nextLink')
64
{
65
do {
66
$URI = $JsonResponse.'@odata.nextLink'
67
$Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing
68
$JsonResponse = $Response.Content | ConvertFrom-Json
69
$DeviceData += $JsonResponse.value
70
} until ($null -eq $JsonResponse.'@odata.nextLink')
71
}
72
73
74
#############################################
75
## ORGANISE THE DATA INTO USEABLE DATASETS ##
76
#############################################
77
78
# Seperate by OS
79
$WindowsDevices = $DeviceData | where {$_.operatingSystem -eq "Windows"}
80
$iOSDevices = $DeviceData | where {$_.operatingSystem -eq "iOS"}
81
$AndroidDevices = $DeviceData | where {$_.operatingSystem -eq "Android"}
82
$UnknownDevices = $DeviceData | where {$_.operatingSystem -ne "Android" -and $_.operatingSystem -ne "iOS" -and $_.operatingSystem -ne "Windows"}
83
84
# Set property exclusion lists. These properties will not be included in the final datasets.
85
$AndroidExcludedProperties = @(
86
'activationLockBypassCode',
87
'remoteAssistanceSessionUrl',
88
'remoteAssistanceSessionErrorDetails',
89
'configurationManagerClientEnabledFeatures',
90
'deviceHealthAttestationState',
91
'totalStorageSpaceInBytes',
92
'freeStorageSpaceInBytes',
93
'requireUserEnrollmentApproval',
94
'iccid',
95
'udid',
96
'roleScopeTagIds',
97
'windowsActiveMalwareCount',
98
'windowsRemediatedMalwareCount',
99
'configurationManagerClientHealthState',
100
'configurationManagerClientInformation',
101
'ethernetMacAddress',
102
'physicalMemoryInBytes',
103
'processorArchitecture',
104
'specificationVersion',
105
'skuFamily',
106
'skuNumber',
107
'managementFeatures',
108
'hardwareInformation',
109
'deviceActionResults',
110
'chromeOSDeviceInfo',
111
'retireAfterDateTime',
112
'preferMdmOverGroupPolicyAppliedDateTime',
113
'autopilotEnrolled',
114
'managedDeviceId',
115
'managedDeviceODataType',
116
'managedDeviceReferenceUrl',
117
'usersLoggedOn',
118
'partnerReportedThreatState',
119
'chassisType'
120
)
121
122
$iOSExcludedProperties = @(
123
'activationLockBypassCode',
124
'remoteAssistanceSessionUrl',
125
'remoteAssistanceSessionErrorDetails',
126
'configurationManagerClientEnabledFeatures',
127
'deviceHealthAttestationState',
128
'requireUserEnrollmentApproval',
129
'iccid',
130
'udid',
131
'roleScopeTagIds',
132
'windowsActiveMalwareCount',
133
'windowsRemediatedMalwareCount',
134
'configurationManagerClientHealthState',
135
'configurationManagerClientInformation',
136
'ethernetMacAddress',
137
'physicalMemoryInBytes',
138
'processorArchitecture',
139
'specificationVersion',
140
'skuFamily',
141
'skuNumber',
142
'managementFeatures',
143
'hardwareInformation',
144
'deviceActionResults',
145
'chromeOSDeviceInfo',
146
'retireAfterDateTime',
147
'preferMdmOverGroupPolicyAppliedDateTime',
148
'autopilotEnrolled',
149
'managedDeviceId',
150
'managedDeviceODataType',
151
'managedDeviceReferenceUrl',
152
'usersLoggedOn',
153
'partnerReportedThreatState',
154
'chassisType',
155
'freeStorageSpaceInBytes',
156
'totalStorageSpaceInBytes'
157
)
158
159
$WindowsExcludedProperties = @(
160
'activationLockBypassCode'
161
'chassisType'
162
'jailBroken'
163
'remoteAssistanceSessionUrl'
164
'remoteAssistanceSessionErrorDetails'
165
'phoneNumber'
166
'androidSecurityPatchLevel'
167
'deviceHealthAttestationState'
168
'subscriberCarrier'
169
'meid'
170
'requireUserEnrollmentApproval'
171
'iccid'
172
'udid'
173
'roleScopeTagIds'
174
'configurationManagerClientInformation'
175
'ethernetMacAddress'
176
'physicalMemoryInBytes'
177
'processorArchitecture'
178
'specificationVersion'
179
'managementFeatures'
180
'hardwareInformation'
181
'deviceActionResults'
182
'usersLoggedOn'
183
'chromeOSDeviceInfo'
184
'totalStorageSpaceInBytes'
185
'freeStorageSpaceInBytes'
186
'configurationManagerClientEnabledFeatures'
187
'configurationManagerClientHealthState'
188
'managedDeviceId'
189
'managedDeviceODataType'
190
'managedDeviceReferenceUrl'
191
)
192
193
# Remove the unwanted properties and add some new ones
194
$AndroidDevices = $AndroidDevices | Select-Object -Property * -ExcludeProperty $AndroidExcludedProperties
195
196
$iOSDevices = $iOSDevices | Select-Object -Property *,`
197
@{l="freeStorageSpaceInGB";e={[math]::Round(($_.freeStorageSpaceInBytes / 1GB),2)}},`
198
@{l="totalStorageSpaceInGB";e={[math]::Round(($_.totalStorageSpaceInBytes / 1GB),2)}} `
199
-ExcludeProperty $iOSExcludedProperties
200
201
$WindowsDevices = $WindowsDevices | Select-Object -Property *,`
202
@{l="freeStorageSpaceInGB";e={[math]::Round(($_.freeStorageSpaceInBytes / 1GB),2)}},`
203
@{l="totalStorageSpaceInGB";e={[math]::Round(($_.totalStorageSpaceInBytes / 1GB),2)}}, `
204
@{l="daysSinceLastSync";e={[math]::Round(((Get-Date) - ($_.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0)}}, `
205
@{l="enabledCoMgmtWorkloads_inventory";e={$_.configurationManagerClientEnabledFeatures.inventory}}, `
206
@{l="enabledCoMgmtWorkloads_modernApps";e={$_.configurationManagerClientEnabledFeatures.modernApps}}, `
207
@{l="enabledCoMgmtWorkloads_resourceAccess";e={$_.configurationManagerClientEnabledFeatures.resourceAccess}}, `
208
@{l="enabledCoMgmtWorkloads_deviceConfiguration";e={$_.configurationManagerClientEnabledFeatures.deviceConfiguration}}, `
209
@{l="enabledCoMgmtWorkloads_compliancePolicy";e={$_.configurationManagerClientEnabledFeatures.compliancePolicy}}, `
210
@{l="enabledCoMgmtWorkloads_windowsUpdateForBusiness";e={$_.configurationManagerClientEnabledFeatures.windowsUpdateForBusiness}}, `
211
@{l="enabledCoMgmtWorkloads_endpointProtection";e={$_.configurationManagerClientEnabledFeatures.endpointProtection}}, `
212
@{l="enabledCoMgmtWorkloads_officeApps";e={$_.configurationManagerClientEnabledFeatures.officeApps}}, `
213
@{l="MEMCMClient_state";e={$_.configurationManagerClientHealthState.state}}, `
214
@{l="MEMCMClient_errorCode";e={$_.configurationManagerClientHealthState.errorCode}}, `
215
@{l="MEMCMClient_lastSyncDateTime";e={$_.configurationManagerClientHealthState.lastSyncDateTime}}, `
216
@{l="MEMCMClient_daysSinceLastSync";e={[math]::Round(((Get-Date) - ($_.configurationManagerClientHealthState.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0)}} `
217
-ExcludeProperty $WindowsExcludedProperties
218
219
# Export the data to CSV format
220
$androiddevices | export-csv -Path $env:temp\AndroidDevices.csv -Force -NoTypeInformation
221
$iOSDevices | export-csv -Path $env:temp\iOSDevices.csv -Force -NoTypeInformation
222
$WindowsDevices | export-csv -Path $env:temp\WindowsDevices.csv -Force -NoTypeInformation
223
224
225
###########################################
226
## UPLOAD DATASETS TO AZURE BLOB STORAGE ##
227
###########################################
228
229
$StorageAccount = Get-AzStorageAccount -Name $StorageAccount -ResourceGroupName $ResourceGroup
230
"AndroidDevices","iOSDevices","WindowsDevices" | foreach {
231
Set-AzStorageBlobContent -File "$env:temp\$_.csv" -Container $Container -Blob $_/$_.csv -Context $StorageAccount.Context -Force
232
}
Copied!

Schedule the Runbook

Schedule the Runbook to execute at regular 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
Last modified 3mo ago