The question “when did user X last login” is one often asked, and until recently there was no good way to report on this. Granted, Exchange Online has the LastLogonTime exposed via the Get-MailboxStatistics cmdlet, but what about other services? Well, as part of the latest additions to the Office 365 Reporting API, we can now get the “Last activity date” across Exchange, SharePoint, OneDrive for Business, Skype for Business and Yammer.
To get the report, navigate to the Office 365 Admin Center, Reports, Usage and select the Active Users report. As with other reports in the portal, you can get a Summary view, represented by a flashy graphics, broken down by either user or service, or a detailed view, in the form of a table as shown below:
Both of these Reports can be exported to CSV file directly from the portal, with some additional information exposed in the file, such as License status and License assignment date for each of the services above.
And of course, you can get the data via the Office 365 Reporting API. The new, Graph-based one that is. If you haven’t dealt with it before, some preparations will be needed so make sure to review the documentation here. If you have an app with the necessary permissions registered, simply obtain an access token and query the report endpoint:
The above example will get a token via an application I have already registered and consented to, then use the token to request the Office365ActiveUsers report from the Reporting API endpoint. As detailed in the report documentation, there are three parameters to use with the report: View, Period, Date. In this case, I’ve requested the detailed view for the last 180 days.
You can then use the Invoke-WebRequest or the Invoke-RestMethod cmdlets to actually get the report. Both cmdlets return the same data in the form of a CSV file, which you can then save to the disk or work with in PowerShell. Here’s an example CSV file, with some of the columns hidden to fit it on the screen:
You might note there are two entries with the same UPN – that’s a bug with the current version of the API. The first entry (unlicensed one) is actually a Mail Contact object, as such it doesn’t have UPN. Other than that, the report looks swell 🙂
The report I am leveraging at this time is the Office 365 Active User Detail. We noticed that we have more than 1000 licensed users who have no “activity dates” listed for each of the services inclusively (Exchange, OS, SP, SfB and Teams) in the report. Would it be a safe assumption for us to say that if they have no last activity dates across the each of the listed services that they are effectively not using the service, and we can reclaim those licenses?
That’s the idea, but it’s not uncommon for the built-in reports to lack data. If you want to be certain, you can check against the Unified audit log for a handful of said users, if no relevant events are captured there, they’re not using the service(s).
Is it possible to download such a report using only the powershell command?
That’s what the sample code in the middle of the article does? There is no built-in PowerShell cmdlet if that’s what you are after.