Report on last activity date in Office 365

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 🙂

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *