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 (i.e. the last logged in date). 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:

GET https://graph.microsoft.com/beta/users?$select=displayName,userPrincipalName,signInActivity

Graph explorer example for user last logged in date

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:

Azure AD portal exampleSo 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:

https://graph.microsoft.com/beta/users?$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:

PowerShell example

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:

https://graph.microsoft.com/beta/users?$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.

28 thoughts on “Reporting on user’s last logged in date in Office 365

  1. Daan says:

    Amazing script, thanks for writing this.
    I had one question, in which way do you selsect only 100 records in your script?

    I’m tryin to play with the query but keep running into only 100 records.

    Thanks in advance for your answer!

    Reply
    1. Vasil Michev says:

      Graph returns 100 entries by default, you need to leverage the nextLink element. Here’s an example:

      $GraphUsers = @()
      $uri = "https://graph.microsoft.com/v1.0/users/?$`select=displayName,mail,userPrincipalName,id,userType,signInActivity&`$top=999&`$filter=userType eq 'Member'"
      do {
          $result = Invoke-WebRequest -Headers $AuthHeader1 -Uri $uri
          $uri = $result.'@odata.nextLink'
      
          $GraphUsers += $result.Value
      } while ($uri)
      Reply
  2. Joshua Bines says:

    FYI – This explains the blank ones instead of the 30 days.

    The last interactive sign-in date and time for a specific user. You can use this field to calculate the last time a user signed in to the directory with an interactive authentication method. This field can be used to build reports, such as inactive users. The timestamp represents date and time information using ISO 8601 format and is always in UTC time. For example, midnight UTC on Jan 1, 2014 is: ‘2014-01-01T00:00:00Z’. Azure AD maintains interactive sign-ins going back to April 2020. For more information about using the value of this property, see Manage inactive user accounts in Azure AD.

    https://docs.microsoft.com/en-us/graph/api/resources/signinactivity?view=graph-rest-beta

    Reply
  3. Anoymous says:

    ConvertTo-SecureString : The parameter value “-XXXXXXXXXXXXXXX” is not a valid encrypted string.
    At line:1 char:57
    + … ecret = Get-Content .\ReportingAPIsecret.txt | ConvertTo-SecureString
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [ConvertTo-SecureString], PSArgumentException
    + FullyQualifiedErrorId : ImportSecureString_InvalidArgument,Microsoft.PowerShell.Commands.ConvertToSecureStringCo
    mmand

    Reply
  4. Ash C says:

    Thank you so much for this article. I tried to setup a POC using your code but unfortunately I am getting the following error. TIA for your suggestion.

    Invoke-WebRequest : The remote server returned an error: (403) Forbidden.
    At line:46 char:14
    + … LastLogin = Invoke-WebRequest -Headers $AuthHeader1 -Uri “https://gra …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

    Reply
    1. Vasil Michev says:

      Make sure the application you are using has the necessary permissions.

      Reply
      1. Ash C says:

        thank you. I did add AuditLog.Read.All, Directory.Read.All and User.Read.All application permission but still getting the same error.

        Reply
        1. Vasil Michev says:

          Check the token, jwt.ms or similar sites can decode it.

    2. Kevin J says:

      You must use Application permissions not Delegated permissions.

      Reply
  5. Harsh Damania says:

    Hi,
    I am trying to run this command in my tenant.and it is not working. We have P2 license in my tenant
    code”:”Authentication_RequestFromNonPremiumTenantOrB2CTenant”,”message”:”Neither tenant is B2C or tenant doesn’t have premium license

    Reply
  6. Nimit says:

    Hello ,

    Thank you for your blog @Vasil Michev , its really good one and very helpful.

    Is their any other string : signInActivity , If I need to use my onPrim AD attribute lastlogon , lastlogondate , Is this possible ?

    Thanks in advance.
    Nimit

    Reply
    1. Vasil Michev says:

      No, as the Graph doesnt cover on-premises AD activities.

      Reply
  7. Someone You Prolly Don't Know says:

    I’ve used this and a couple of POC scripts against the graph beta API and I’m getting blank fields for signInActivity.
    Can you advise if the beta site no longer exposes this information (because if not I’m unsure what I’m doing wrong).
    I’m able to pull principal name, create date, etc, just not signInActivity nor lastPasswordChagneDateTime

    Reply
    1. Vasil Michev says:

      It’s working just fine for me on both /beta and /v1.0. Try /v1.0, try against a single user, renew your token, check for proper scopes, etc.

      Reply
    2. Alok Dubey says:

      If you are running the query in Graph Explorer, make sure you have AuditLog.ReadAll permission added to the account you are logged in with.
      Query: I am running this query to get the guest users list
      GET https://graph.microsoft.com/beta/users?$filter=userType eq ‘Guest’&$select=displayname,signInActivity
      Accounts which has never logged into cloud service will show up like below in result:
      {
      “displayName”: “XXXXXXXX”,
      “id”: “XXXXXX-XXX-XXXXXXXXXXXX”
      },
      Accounts who have logged into cloud services ever will show up like below:
      {
      “displayName”: “XXXXX XXXXX”,
      “id”: “XXXXX-XXXX-4493-XXXX3-XXXXXXde”,
      “signInActivity”: {
      “lastSignInDateTime”: “2020-07-31T08:25:25Z”,
      “lastSignInRequestId”: “XXXXX7f-XXc5-XX2e-XXXc-XXXXXX00”
      }
      },

      Reply
      1. I am curious as well says:

        Its interesting I was able to see results in Graph explorer but with same admin account when trying to run the REST API call like: GET https://graph.microsoft.com/beta/users?$filter=userType eq ‘Guest’&$select=displayname,signInActivity, via some sort of:
        $SignRequestParams= @{
        Method = ‘GET’
        Uri = “https://graph.microsoft.com/beta/users?$filter=userType eq ‘Guest’&$select=displayname,signInActivity”
        Headers = @{
        ‘Authorization’ = “Bearer $Token”
        }
        }
        $RequestSign = Invoke-RestMethod @SignRequestParams,

        the values for signinactivity are shown blank in the $RequestSign variable (I am running a loop to get all the tenant values for guests and all are empty even if I know for a fact theres activity during last 12 months and also I can see results in Graph explorer SAME query I a, using here as the base for my REST API … ) …

        any idea why? (I am using delegated permissions and generate tokens sound as I can see the display names, ID and UPNs for the guest users etc …

        Reply
        1. Gavin Stone says:

          So yeh, it turns out that when this is happening it’s because to get at the SignInActivity property you need Azure AD p1/p2. Huge rabbit hole which I hope to save someone else time from.

  8. 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
    https://graph.microsoft.com/beta/users/{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.

    Reply
    1. Vasil Michev says:

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

      Reply
  9. 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’

    Cheers

    Reply
    1. 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.

      Reply
  10. 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?

    Reply
    1. 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.

      Reply

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.