Skip to content

Instantly share code, notes, and snippets.

@darrenjrobinson

darrenjrobinson/getM365AppUserDetail.ps1

Last active February 2, 2023 12:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save darrenjrobinson/166fccc73e9834eccbf522b9940dc4ea to your computer and use it in GitHub Desktop.
Save darrenjrobinson/166fccc73e9834eccbf522b9940dc4ea to your computer and use it in GitHub Desktop.
Download ZIP
Getting Microsoft 365 Individual User Usage Report with PowerShell. Associated Blogpost https://blog.darrenjrobinson.com/getting-microsoft-365-individual-user-usage-reports-with-powershell/
Raw
getM365AppUserDetail.ps1
Function AuthN {
<#
.SYNOPSIS
Authenticate to Azure AD and receieve Access and Refresh Tokens.
.DESCRIPTION
Authenticate to Azure AD and receieve Access and Refresh Tokens.
.PARAMETER tenantID
(required) Azure AD TenantID.
.PARAMETER credential
(required) ClientID and ClientSecret of the Azure AD registered application with the necessary permissions.
.EXAMPLE
$myCred = Get-Credential
AuthN -credential $myCred -tenantID '74ea519d-9792-4aa9-86d9-abcdefgaaa'
.LINK
http://darrenjrobinson.com/
#>
[cmdletbinding()]
param(
[Parameter(Mandatory = $true, ValueFromPipeline = $true)]
[string]$tenantID,
[Parameter(Mandatory = $true, ValueFromPipeline = $true)]
[System.Management.Automation.PSCredential]$credential
)
if (!(get-command Get-MsalToken)) {
Install-Module -name MSAL.PS -Force -AcceptLicense
}
try {
# Authenticate and Get Tokens
$token = Get-MsalToken -ClientId $credential.UserName -ClientSecret $credential.Password -TenantId $tenantID
return $token
}
catch {
$_
}
}
Function GetM365UserActivity {
<#
.SYNOPSIS
Get M365 User Activity.
.DESCRIPTION
Get M365 User Activity.
.PARAMETER days
(optional - Defaults to 7 Days) Days to report on. Accepted values are 7, 30, 90, and 180
.EXAMPLE
GetM365UserActivity
.EXAMPLE
GetM365UserActivity -days 30
.LINK
http://darrenjrobinson.com/
#>
[cmdletbinding()]
param(
[Parameter(Mandatory = $false, ValueFromPipeline = $true)]
[ValidateSet("7", "30", "90", "180")]
[string]$days
)
# Refresh Access Token
$global:myToken = AuthN -credential $myCred -tenantID $myTenantId
try {
if ($days) {
# Get M365 User Activity
$m365Activity = Invoke-RestMethod -Headers @{Authorization = "Bearer $($myToken.AccessToken)" } `
-Uri "https://graph.microsoft.com/beta/reports/getM365AppUserDetail(period='D$($days)')/content?$format=application/json" `
-Method Get
}
else {
# Get M365 User Activity
$m365Activity = Invoke-RestMethod -Headers @{Authorization = "Bearer $($myToken.AccessToken)" } `
-Uri "https://graph.microsoft.com/beta/reports/getM365AppUserDetail(period='D7')/content?$format=application/json" `
-Method Get
}
return $m365Activity
}
catch {
$_
}
}
# Globals
# Tenant ID
$global:myTenantId = '74ea519d-9792-4aa9-86d9-abcdefgaaa'
# Registered AAD App ID and Secret
$global:myCred = [pscredential]::new("1c29e80e-ec64-43f7-b07a-1324567890", ("UEy9yEnU6vcCLzdZm+123ABC456DEFyjyL2nYQeU=" | ConvertTo-SecureString -AsPlainText -Force))
# Report Days
$reportDays = 90
<#
M365 User Activity
#>
Import-Module ImportExcel
Import-Module MSAL.PS
$m365UserActivityData = GetM365UserActivity -days $reportDays
$m365UserActivityData = $m365UserActivityData.replace("", "")
$m365UserActivityConverted = $m365UserActivityData | convertfrom-csv
"Report Data for $($m365UserActivityConverted.Count) Users retrieved...."
$m365UserActivityConverted | Export-Excel -path "./M365UserUsageReport-$($reportDays)Days.xlsx" -AutoSize -AutoFilter -WorksheetName M365UserUsage -ConditionalText $(
New-ConditionalText 'No' DarkRed LightPink
New-ConditionalText 'Yes' DarkGreen LightGreen
)
@oasec1
Copy link

oasec1 commented Sep 8, 2021

Darren,

Your script is throwing an error in the excel export operations. can you help?

Method invocation failed because [System.Management.Automation.ErrorRecord] does not contain a method named 'replace'.
At C:\users\james\desktop\powershell scripts\getM365AppUserDetail.ps1:103 char:1

  • $m365UserActivityData = $m365UserActivityData.replace("", "")
  •   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
  + FullyQualifiedErrorId : MethodNotFound

Report Data for 0 Users retrieved....
Error applying conditional formatting to worksheet Cannot bind argument to parameter 'Address' because it is null.
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.2.2\Public\Export-Excel.ps1:643 char:20

  • ... catch {throw "Error applying conditional formatting to worksheet ...
  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : OperationStopped: (Error applying ...use it is null.:String) [], RuntimeException
    • FullyQualifiedErrorId : Error applying conditional formatting to worksheet Cannot bind argument to parameter 'Address' because it is null.

Thank you for your assistance and the great PowerShell script!
Robert

@darrenjrobinson
Copy link
Author

Hey Robert. Comment out line 114 of the script and see what happens.
Inspect the value of $m365UserActivityData after running and see if there is any anomalous characters at the beginning of the output. You may need to then update line 144 adding it back in, but replacing changing the anomalous characters to remove in order to allow ingestion to Excel.

@oasec1
Copy link

oasec1 commented Sep 9, 2021 via email

@oasec1
Copy link

oasec1 commented Sep 9, 2021 via email

@oasec1
Copy link

oasec1 commented Sep 9, 2021 via email

@darrenjrobinson
Copy link
Author

Robert, (401) Unauthorized means that either the credentials that you updated in line 101 are incorrect or don't match your tenant that you updated on line 99 for the AAD Application you registered them with as per the blog post https://blog.darrenjrobinson.com/getting-microsoft-365-individual-user-usage-reports-with-powershell/

@oasec1
Copy link

oasec1 commented Sep 10, 2021 via email

@darrenjrobinson
Copy link
Author

What is in the $m365UserActivityData and $m365UserActivityConverted objects before being passed to Export-Excel ??

@oasec1
Copy link

oasec1 commented Sep 10, 2021 via email

@darrenjrobinson
Copy link
Author

That explains why the .replace line was failing. Also why the export to excel is failing. You have not returned any data.
Assuming their has been user activity in the associated tenant, try increasing the number of days to report on.
Also make sure you have the right permission to read the data. Your AAD Registered App requires Application Permissions for the Reports.Read.All scope.

@oasec1
Copy link

oasec1 commented Sep 10, 2021 via email

@CloudLearner7894144
Copy link

CloudLearner7894144 commented Dec 6, 2022 •
edited

Hey Darren,

Thank you for all your work on this script and for sharing it with the world.

Unfortunately, I have not been able to get it working. I get the following error:

PS D:\Users\xxx\Desktop> D:\Users\ryan_\Desktop\getM365AppUserDetail.ps1
Report Data for 0 Users retrieved....
Error applying conditional formatting to worksheet Cannot bind argument to parameter 'Address' because it is null.
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.3\Public\Export-Excel.ps1:651 char:21

  • ... catch { throw "Error applying conditional formatting to worksheet ...
  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : OperationStopped: (Error applying ...use it is null.:String) [], RuntimeException
    • FullyQualifiedErrorId : Error applying conditional formatting to worksheet Cannot bind argument to parameter 'Address' because it is null.

PS D:\Users\xxx\Desktop> $m365UserActivityData
Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At D:\Users\ryan_\Desktop\getM365AppUserDetail.ps1:67 char:29

  • ... 5Activity = Invoke-RestMethod -Headers @{Authorization = "Bearer $($m ...

  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    • FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    Note that I comment out the following line, as per your earlier suggestion.

    #$m365UserActivityData = $m365UserActivityData.replace("", "")

Any ideas would be much appreciated.

Thank you :)

@smccnn1
Copy link

smccnn1 commented Feb 2, 2023

Hey @CloudLearner7894144

I had the same issue as you. After searching a lot, I made some modifications to the file, and it worked. I cannot remember what I did (other than editing lines 80 & 86), but I have uploaded the working .ps1 to my git (its my first time doing this).

You can access it at this link: https://gist.github.com/smccnn1/9102bc8040a8486af2406d69033c3ca5#file-getm365appuserdetail-ps1

I hope it works for you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment