Reporting on user’s last logged in date in Office 365

After a long, long wait, Microsoft is finally addressing one of the most common requests from Office 365/Microsoft 365/Azure AD admins – the ability to easily check when was the last time a given user logged in to the service. Up until now, this was only possible by crawling the Azure AD sign-in logs or the Unified audit log in the Security and Compliance Center, which was doable, but unnecessary complicated task. Now, it’s as easy as just looking at the properties of the user object:

As the name of the property (and it members) suggests, we are effectively looking at the last entry for said user in the sign-in logs, and you can easily confirm this by opening the Azure AD blade -> Sign-ins and filtering it:

So how do we go about generating a report of the last login date for all our users? The latest versions of the AzureAD/AzureADPreview modules do not expose the property, and neither does the good old MSOnline module, so we need to get it by querying the Graph API directly. In particular, the following URI will give us a list of all the users, their UPN and the signInActivity value, out of which we can extract the Last login date:

I’ve put together a small proof-of-concept script on this, that you can get on GitHub. As usual, few remarks are in order. The script uses the “client app” flow to obtain an access token, meaning it assumes that you already have an application registered in Azure AD, by using the application permissions model, and have granted the necessary permissions on it (User.Read.All, Directory.Read.All, Auditlogs.Read.All). The client secret for said app is stored as a secure string and passed along.

The token is obtained by making a POST request against the v2.0 /token endpoint, and there’s little to no error handling included. If you plan on using the sample in production, you might want to address this, or replace it with your own “get/renew token” routine, use the ADAL/MSAL binaries or whatever.

Once a token is successfully obtained, a single request is made to the Graph API to the endpoint mentioned above, and the result is stored in the $result variable, then transformed and displayed in the console window. The default sorting is used, meaning entries will be ordered by the UPN value. In case you have a large number of users, it’s of course best to export the result in a CSV file, which you can do by uncommenting the last line. Here’s a sample of the output:

There are some empty entries, which correspond to users that haven’t been logged in recently, if at all. Since we have some entries that are over 30 days old, this indicates Azure AD is now keeping data beyond the range available in the sign-ins blade or by querying the auditLogs/signIns endpoint directly. This is most likely just a single entry representing the last login date, but you never know.

Now, since this is obviously still in beta, things might change in the future. One thing I’d like to see addressed is the method for getting the Last login date for a single user. Currently, if you try the “direct” approach by using the /users/UPN endpoint, the signInActivity property will not be exposed. If you modify the query to specifically include a select statement for the signInActivity property, the output is glitchy and returns details for multiple users. Instead, you should use a filter as the one below:

A bit counter-intuitive, but at least it works. You can see some additional examples in the documentation.