Getting Microsoft 365 Individual User Usage Report with PowerShell. Associated Blogpost https://blog.darrenjrobinson.com/getting-microsoft-365-individual-user-usage-reports-with-powershell/

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
)

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 :)

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!