I like knowing what specific parts of Office365 my clients use most, so I can customize their experience to the way they work. This means I can send them manuals for mobile usage when they are only using mobile phones, or I can help them in using Teams, Onedrive, or other stuff like Planner or ToDo to the fullest.
I also like using the usage report as an early alerting measure – but that’ll be a different blog this week. To get the reports, we’ll be using the Secure Application Model. We will need to add a single permission first. Do the following to create this permission:
- Go to the Azure Portal.
- Click on Azure Active Directory, now click on “App Registrations”.
- Find your Secure App Model application. You can search based on the ApplicationID.
- Go to “API Permissions” and click Add a permission.
- Choose “Microsoft Graph” and “Application permission”.
- Search for “Reports” and click on “Reports.Read.All”. Click on add permission
- Do the same for “Delegate Permissions”.
- Finally, click on “Grant Admin Consent for Company Name.
After giving these permissions, you can start running either of the scripts below. These gather all the usage reports that are available via the Azure AD Graph API for all your clients. These reports contain information like how many files are stored in Onedrive, what applications the client uses, and how many office activations the client has. I’ve listed all the ones that I like – Feel free to strip out the ones you do not use.
Generic Version
I’ve been asked a couple of times to include a screenshot of how the report could look. This is a template of one of my testing tenants, please note that with production tenants this will most likely be a much longer list. 🙂
write-host “Connecting to MSOLService” -ForegroundColor Green
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
write-host “Grabbing client list” -ForegroundColor Green
$customers = Get-MsolPartnerContract -All
write-host “Connecting to clients” -ForegroundColor Green
foreach ($customer in $customers) {
write-host “Generating token for $($Customer.name)” -ForegroundColor Green
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes ‘https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $customer.TenantID
$Header = @{
Authorization = “Bearer $($graphToken.AccessToken)”
}
write-host “Gathering Reports for $($Customer.name)” -ForegroundColor Green
#Gathers which devices currently use Teams, and the details for these devices.
$TeamsDeviceReportsURI = “https://graph.microsoft.com/v1.0/reports/getTeamsDeviceUsageUserDetail(period='D7')"
$TeamsDeviceReports = (Invoke-RestMethod -Uri $TeamsDeviceReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Teams device report” | Out-String
#Gathers which Users currently use Teams, and the details for these Users.
$TeamsUserReportsURI = “https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(period='D7')"
$TeamsUserReports = (Invoke-RestMethod -Uri $TeamsUserReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Teams user report"| Out-String
#Gathers which users currently use email and the details for these Users
$EmailReportsURI = “https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D7')"
$EmailReports = (Invoke-RestMethod -Uri $EmailReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Email users Report"| Out-String
#Gathers the storage used for each e-mail user.
$MailboxUsageReportsURI = “https://graph.microsoft.com/v1.0/reports/getMailboxUsageDetail(period='D7')"
$MailboxUsage = (Invoke-RestMethod -Uri $MailboxUsageReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Email storage report"| Out-String
#Gathers the activations for each user of office.
$O365ActivationsReportsURI = “https://graph.microsoft.com/v1.0/reports/getOffice365ActivationsUserDetail"
$O365ActivationsReports = (Invoke-RestMethod -Uri $O365ActivationsReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “O365 Activation report"| Out-String
#Gathers the Onedrive activity for each user.
$OneDriveActivityURI = “https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(period='D7')"
$OneDriveActivityReports = (Invoke-RestMethod -Uri $OneDriveActivityURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Onedrive Activity report"| Out-String
#Gathers the Onedrive usage for each user.
$OneDriveUsageURI = “https://graph.microsoft.com/v1.0/reports/getOneDriveUsageAccountDetail(period='D7')"
$OneDriveUsageReports = (Invoke-RestMethod -Uri $OneDriveUsageURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “OneDrive usage report"| Out-String
#Gathers the Sharepoint usage for each user.
$SharepointUsageReportsURI = “https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D7')"
$SharepointUsageReports = (Invoke-RestMethod -Uri $SharepointUsageReportsURI -Headers $Header -Method Get -ContentType “application/json”) -replace “”, "” | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent “Sharepoint usage report"| Out-String
$head =
@”
$head,$TeamsDeviceReports,$TeamsUserReports,$EmailReports,$MailboxUsage,$O365ActivationsReports,$OneDriveActivityReports,$OneDriveUsageReports,$SharepointUsageReports | out-file “C:\Temp$($Customer.name).html”
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
|
#### **IT-Glue version**
```powershell
########################## Office 365 ############################
$ApplicationId = 'YourApplicationID'
$ApplicationSecret = 'SecretApplicationSecret' | Convertto-SecureString -AsPlainText -Force
$TenantID = 'YourTenantID'
$RefreshToken = 'SuperSecretRefreshToken'
$upn = 'UPN-Used-To-Generate-Tokens'
########################## IT-Glue ############################
$APIKEy = "ITGLUEAPIEY"
$APIEndpoint = "https://api.eu.itglue.com"
$FlexAssetName = "Office365 Reports - AutoDoc v1"
$Description = "Office365 Reporting."
#some layout options, change if you want colours to be different or do not like the whitespace.
$TableHeader = "<table class=`"table table-bordered table-hover`" style=`"width:80%`">"
$TableStyling = "<th>", "<th style=`"background-color:#4CAF50`">"
###########################
#Grabbing ITGlue Module and installing.
If (Get-Module -ListAvailable -Name "ITGlueAPI") {
Import-module ITGlueAPI
}
Else {
Install-Module ITGlueAPI -Force
Import-Module ITGlueAPI
}
#Settings IT-Glue logon information
Add-ITGlueBaseURI -base_uri $APIEndpoint
Add-ITGlueAPIKey $APIKEy
write-host "Checking if Flexible Asset exists in IT-Glue." -foregroundColor green
$FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
if (!$FilterID) {
write-host "Does not exist, creating new." -foregroundColor green
$NewFlexAssetData =
@{
type = 'flexible-asset-types'
attributes = @{
name = $FlexAssetName
icon = 'sitemap'
description = $description
}
relationships = @{
"flexible-asset-fields" = @{
data = @(
@{
type = "flexible_asset_fields"
attributes = @{
order = 1
name = "Teams Device Reports"
kind = "Textbox"
required = $true
"show-in-list" = $true
"use-for-title" = $true
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 2
name = "Teams User Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 3
name = "Email Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 4
name = "Mailbox Usage Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 5
name = "O365 Activations Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 6
name = "OneDrive Activity Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 7
name = "OneDrive Usage Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 8
name = "Sharepoint Usage Reports"
kind = "Textbox"
required = $false
"show-in-list" = $false
}
},
@{
type = "flexible_asset_fields"
attributes = @{
order = 9
name = "TenantID"
kind = "Text"
required = $false
"show-in-list" = $false
}
}
)
}
}
}
New-ITGlueFlexibleAssetTypes -Data $NewFlexAssetData
$FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
}
$AllITGlueContacts = @()
#Grab all IT-Glue contacts to match the domain name.
write-host "Getting IT-Glue contact list" -foregroundColor green
$i = 0
do {
$AllITGlueContacts += (Get-ITGlueContacts -page_size 1000 -page_number $i).data.attributes
$i++
Write-Host "Retrieved $($AllITGlueContacts.count) Contacts" -ForegroundColor Yellow
}while ($AllITGlueContacts.count % 1000 -eq 0 -and $AllITGlueContacts.count -ne 0)
write-host "Start documentation process." -foregroundColor green
$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
write-host "Generating access tokens" -ForegroundColor Green
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID
write-host "Connecting to MSOLService" -ForegroundColor Green
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
write-host "Grabbing client list" -ForegroundColor Green
$customers = Get-MsolPartnerContract -All
write-host "Connecting to clients" -ForegroundColor Green
foreach ($customer in $customers) {
write-host "Generating token for $($Customer.name)" -ForegroundColor Green
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $customer.TenantID
$Header = @{
Authorization = "Bearer $($graphToken.AccessToken)"
}
write-host "Gathering Reports for $($Customer.name)" -ForegroundColor Green
#Gathers which devices currently use Teams, and the details for these devices.
$TeamsDeviceReportsURI = "https://graph.microsoft.com/v1.0/reports/getTeamsDeviceUsageUserDetail(period='D7')"
$TeamsDeviceReports = (Invoke-RestMethod -Uri $TeamsDeviceReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Teams device report</h1>" | Out-String
#Gathers which Users currently use Teams, and the details for these Users.
$TeamsUserReportsURI = "https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(period='D7')"
$TeamsUserReports = (Invoke-RestMethod -Uri $TeamsUserReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Teams user report</h1>" | Out-String
#Gathers which users currently use email and the details for these Users
$EmailReportsURI = "https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D7')"
$EmailReports = (Invoke-RestMethod -Uri $EmailReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Email users Report</h1>" | Out-String
#Gathers the storage used for each e-mail user.
$MailboxUsageReportsURI = "https://graph.microsoft.com/v1.0/reports/getMailboxUsageDetail(period='D7')"
$MailboxUsage = (Invoke-RestMethod -Uri $MailboxUsageReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Email storage report</h1>" | Out-String
#Gathers the activations for each user of office.
$O365ActivationsReportsURI = "https://graph.microsoft.com/v1.0/reports/getOffice365ActivationsUserDetail"
$O365ActivationsReports = (Invoke-RestMethod -Uri $O365ActivationsReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>O365 Activation report</h1>" | Out-String
#Gathers the Onedrive activity for each user.
$OneDriveActivityURI = "https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(period='D7')"
$OneDriveActivityReports = (Invoke-RestMethod -Uri $OneDriveActivityURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Onedrive Activity report</h1>" | Out-String
#Gathers the Onedrive usage for each user.
$OneDriveUsageURI = "https://graph.microsoft.com/v1.0/reports/getOneDriveUsageAccountDetail(period='D7')"
$OneDriveUsageReports = (Invoke-RestMethod -Uri $OneDriveUsageURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>OneDrive usage report</h1>" | Out-String
#Gathers the Sharepoint usage for each user.
$SharepointUsageReportsURI = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D7')"
$SharepointUsageReports = (Invoke-RestMethod -Uri $SharepointUsageReportsURI -Headers $Header -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv | ConvertTo-Html -fragment -PreContent "<h1>Sharepoint usage report</h1>" | Out-String
$FlexAssetBody =
@{
type = 'flexible-assets'
attributes = @{
traits = @{
'teams-device-reports' = ($TableHeader + $TeamsDeviceReports) -replace $TableStyling
'teams-user-reports' = ($TableHeader + $TeamsUserReports ) -replace $TableStyling
'email-reports' = ($TableHeader + $EmailReports) -replace $TableStyling
'mailbox-usage-reports' = ($TableHeader + $MailboxUsage) -replace $TableStyling
'o365-activations-reports' = ($TableHeader + $O365ActivationsReports) -replace $TableStyling
'onedrive-activity-reports' = ($TableHeader + $OneDriveActivityReports) -replace $TableStyling
'onedrive-usage-reports' = ($TableHeader + $OneDriveUsageReports) -replace $TableStyling
'sharepoint-usage-reports' = ($TableHeader + $SharepointUsageReports) -replace $TableStyling
'tenantid' = $customer.TenantId
}
}
}
Write-Host " Finding $($customer.name) in IT-Glue" -ForegroundColor Green
$orgID = @()
$customerdomains = Get-MsolDomain -TenantId $customer.tenantid
foreach ($customerDomain in $customerdomains) {
$orgID += ($AllITGlueContacts | Where-Object { $_.'contact-emails'.value -match $customerDomain.name }).'organization-id' | Select-Object -Unique
}
write-host " Uploading Reports $($customer.name) into IT-Glue" -ForegroundColor Green
foreach ($org in $orgID) {
$ExistingFlexAsset = (Get-ITGlueFlexibleAssets -filter_flexible_asset_type_id $($filterID.ID) -filter_organization_id $org).data | Where-Object { $_.attributes.traits.'tenantid' -eq $customer.TenantId }
#If the Asset does not exist, we edit the body to be in the form of a new asset, if not, we just upload.
if (!$ExistingFlexAsset) {
$FlexAssetBody.attributes.add('organization-id', $org)
$FlexAssetBody.attributes.add('flexible-asset-type-id', $($filterID.ID))
write-host " Creating Reports $($customer.name) into IT-Glue organisation $org" -ForegroundColor Green
New-ITGlueFlexibleAssets -data $FlexAssetBody
start-sleep 2
}
else {
write-host " Updating Reports $($customer.name) into IT-Glue organisation $org" -ForegroundColor Green
$ExistingFlexAsset = $ExistingFlexAsset | select-object -last 1
Set-ITGlueFlexibleAssets -id $ExistingFlexAsset.id -data $FlexAssetBody
start-sleep 2
}
}
}
|
So that’s it! As always, Happy PowerShelling!
