Table of Contents
The GetGraphUserStatisticsReport.PS1 Script
Last June, I wrote about a PowerShell script to interrogate the Microsoft Graph to retrieve usage data from workloads like Exchange Online, SharePoint Online, and Teams. Some of this data is available via PowerShell cmdlets like Get-ExoMailboxStatistics and Get-SPOSite, but using the Graph is usually faster.
Find Inactive Accounts
The idea behind the script is that if you retrieve usage information from a bunch of workloads, you have the basis to figure out what accounts are in active use. Potentially, you might even find some accounts that haven’t been used for a while that you can archive or remove to save some Office 365 licenses. Figure 1 shows the script output as viewed through the Out-GridView cmdlet.
The original script worked, but it had some performance issues once people tried to run it in tenants with thousands of accounts. Long-running PowerShell scripts are not usual, but the issues people ran into pointed to unreasonable delays.
GitHub Contributors
One of the advantages of storing scripts on GitHub is that others can work with the code to detect where problems might lurk. In this case, suspicion fell on how the script processed data in the array storing information fetched from the Graph. It was suggested that better results might be achieved by changing to a PowerShell DataTable object. I did some work to test the assertion but didn’t see a huge gain, probably because I don’t have a big enough tenant to test.
In any case, a simpler fix became obvious. Instead of scanning the big list containing all the records for the records (usually six) for each user, it’s much better to extract the records for the user into an array and use that array. The change is made in version 1.2 of the script, which is now available to download from GitHub. (update: The current version of the script is 2.0).
It would be helpful if those who have admin permissions in tenant with more than a few thousand accounts could test the script (after carefully checking that the script is OK to run in your tenant, creating the necessary registered app in Azure AD, and so on). If you find problems, record them in GitHub and the community will have a chance to work the issues.
V1.3 Update
We’ve just updated the script to V1.3 to incorporate another speed tweak. The Where method is more appropriate and faster when you only want to extract data from a set and don’t need to feed that data through the pipeline. The script extracts records for each user to create an assessment of the user’s activity and using the Where method is faster… A good lesson learned!
Learn more about Office 365 tenant administration by subscribing to the only eBook that’s updated monthly: Office 365 for IT Pros.
Hello Tony,
Thank you for this great effort, I’ve one question, what do you mean by the $AppID in this script?
The $AppId variable points to the GUID for the application registered in Azure AD which holds the permissions necessary to access the data.
Tony,
Thank you! This is a terrific report. In my company, we have employees spanning ~90 countries. Can you recommend a change I could make to the script to retrieve the user’s Country and/or UsageLocation property and include one or both properties in the final CSV output?
What I think I would do is create a table of user data from Azure AD keyed off the user principal name and read that to fetch information about accounts to include in the output report. That’s probably the simplest thing to do. You can fetch the user data by running a command like this:
[array]$UserData = Get-MgUser -All -Filter “userType eq ‘Member'”
And then to look up the information:
$UserInfo = $UserData | Where-Object {$_.Id -eq $U}
After that, you can extract the different properties from the $UserInfo object.
Many Thanks!!