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:$select=displayName,userPrincipalName,signInActivity

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:$filter=startswith(displayName,'vasil')&$select=displayName,signInActivity

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

This entry was posted in Uncategorized. Bookmark the permalink.

8 Responses to Reporting on user’s last logged in date in Office 365

  1. Max says:

    Hi Vasil,

    Thanks for this, really useful. It looks like things have moved on slightly now and I am able to get the signInActivity when specifying a user by their ID{ID}?$select=displayName,userPrincipalName,signInActivity,ID
    The UPN still doesn’t work, but querying by the ID seems to get the signInActivity for a given user.

    • Vasil Michev says:

      Thanks Max, I fully expect it to be a “regular” property once it goes GA, even exposed in PowerShell and such.

  2. Steve says:

    Hi Vasil
    I am testing your script in our environment but it only dumps 100 entries (tenant has 3000+ accounts).
    Furthermore, while I see the LastLoginDate in the screen dump, it’s not in the exported csv file.
    There I have DisplayName, UserPrincipalName and id (the guid of the account).
    I’m probably doing something wrong but I can’t figure out what it is.

    P.S. I also had to add -AsPlainText to the ConvertTo-SecureString line because otherwise it would keep erroring out with ‘Input string was not in a correct format’


    • Vasil Michev says:

      It’s a simple proof of concept script mate, all it does it illustrate the point. If you want a production-ready script, you’d have to add your own authentication routine, pagination to handle larger user counts, some error handling, etc. You can take a look at some of my larger script samples if you need a hint on how to do all that.

      For the export, you’d probably need to select the property explicitly.

  3. Dawid says:

    Thanks a lot for this article , helped me a lot!

  4. Anonymous Consultant says:

    Our team is working on developing functionality that can really benefit with this feature. Do you know Microsoft’s timeline to release this feature for use in production?

    • Vasil Michev says:

      Nope, but the data is there regardless, if you don’t want to use the beta APIs you can get it out of the sign-in logs.

  5. Pingback: Microsoft 365 Naujienos – Balandžio 27, 2020 [LT] – /w/root

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.