List inactive users via Graph API

Sometimes it can be useful to get an overview of the inactive users. Microsoft has several features for that. Today I will show you how to retrieve this type of information via the Graph API in Power Automate.

Inspiration

This question from Ashok2:

I have SharePoint list where we have people picker column ( Team Member) around 190 rows now few of users are left organization and not valid can we run a flow which identify those users and mark a flag or delete them.

Power Users Community thread: Flow to Flag users who are not active.

signInActivity resource type

In this scenario I am looking at inactive users. My definition of an inactive user is that the user has not signed in for x period of time, in this scenario at least 90 days ago. Luckily Microsoft has the signInActivity resource type in the Graph API which we can use. This can be used as a property in a filter with the List Users method.

To retrieve this information from the Graph API you do require AuditLog.Read.All & Directory.Read.All permissions.

Active Directory OAuth

To use Graph API in Power Automate you shouldn’t forget to register an App in Azure Active Directory, you will need that for the Authentication section of the HTTP action in the flow setup. Like mentioned in a couple of my other blogposts, Elaiza Benitez has written a nice blogpost about this, How to authenticate as an application with Microsoft Graph API with flow.

M365 CLI

Alternatively, you could use code to create this App in Azure Active Directory. Waldek Mastykarz has a nice blog about creating this with only one line of code with M365 CLI, Create Azure AD apps with one line of code.

You can run this from Windows, macOS or Linux, using Bash, Cmder or PowerShell. Whatever you prefer!

Don’t forget to grant consent after you create it btw 😉

mygraphapi_m365cli

Flow setup

Before you start: be aware the HTTP action is part of a premium connector.

listinactiveusers_graphapi

1. Add a Manually trigger a flow trigger action.

2. Add a Send an HTTP action. Use a GET method and make sure it uses the following URI.

The URI is using the AddDays and UtcNow functions to calculate a date which is 90 days ago. The query will only retrieve users which have a lastSignInDateTime which is less or equal (le) to that date of 90 days ago.

http_inactiveusers

3. Use the Authentication setup of the table below.

FieldValue
AuthenticationAzure Active Directory OAuth
Authorityhttps://login.microsoftonline.com
TenantGUID of your tenant
Audiencehttps://graph.microsoft.com
Client IDGUID of the app you created earlier
Credential TypeSecret
SecretSecret you just generated

http_graph_auth

4. Add a Select action. Use the value dynamic content field in the From. In the mapping use whatever properties you want to see from the inactive users. In my example I used the userPrincipalName and the lastSignInDateTime properties.

select_inactiveusers

That should be it for the setup.

When you run it you should see a result like below.

lastsign_test

Happy testing!

You may also like...

14 Responses

  1. Dustin says:

    Thanks for this guide. I’ve got everything setup, but when it comes to the Select step, I have no dynamic content available for the HTTP action beyond Body, Headers, and Status code. If I run the Flow without the Select step I can see it runs successfully based on the raw outputs, but it’s not overly helpful in this format. Are you able to share the expressions for the Select step?

  2. Dennis says:

    Hi Dustin,

    The expressions are in the code snippet just above the screenshot.
    https://gist.github.com/expiscornovus/71c1d0f9a1c619c185e147d877f5cf6e#file-selectinactiveusers-json

    From
    body(‘HTTP’)[‘value’]

    UPN
    item()[‘userPrincipalName’]

    lastSignInDateTime
    item()[‘signInActivity’][‘lastSignInDateTime’]

  3. Rick says:

    Excellent post! Thanks for the insight. I’m a little confused on where the code snippet goes in the Select action. Please advise.

  4. Dennis says:

    Hi Rick,

    The expression body(‘HTTP’)[‘value’] goes into the From field.

    The expression item()[‘userPrincipalName’] goes into the value field of the UPN key within the Map field
    The expression item()[‘signInActivity’][‘lastSignInDateTime’] goes into the value field of the lastSignInDateTime key within the Map field

    Hopefully that makes more sense.

    Let me also have a look if I can update the post with a bit more explanation.

  5. Rick says:

    Thanks for your swift response. So that’s what I did in the beginning, but it still fails. Not sure what I’m missing. I also made sure that the permissions on the Graph API App registration are set correctly. I receive the error below:

    InvalidTemplate. The execution of template action ‘Select’ failed: The evaluation of ‘query’ action ‘where’ expression ‘{ “UPN”: “@item()[‘userPrincipalName’]”, “lastSignInDateTime”: “@item()[‘signInActivity’][‘lastSignInDateTime’]” }’ failed: ‘The template language expression ‘item()[‘userPrincipalName’]’ cannot be evaluated because property ‘userPrincipalName’ doesn’t exist, available properties are ‘id, signInActivity’. Please see https://aka.ms/logicexpressions for usage details.’.

  6. Rick says:

    So I think I might see what the issue is with receiving the error above….I think. When I look at the output of the HTTP after the flow fails, it shows there are entries with ID and SigninActivity, but no UPN. I’m not sure if these are deleted users or some kind of orphaned object somewhere. I’m guessing this can be optimized to filter out those entries maybe with an array. Just not too sure how I’d resolve it.

  7. Dennis says:

    Hi Rick,

    In that case try adding the ? character in the expressions.

    UPN
    item()?['userPrincipalName']

    lastSignInDateTime
    item()?['signInActivity']?['lastSignInDateTime']

  8. Rick says:

    Thank you so much! That seemed to have worked. Is there any way to filter those out before the select? I’m outputting the data to a SPO List and it doesn’t fill in the fields for UPN and lastSignInDateTime if those fields are missing; even for a few. I was trying a filter in the Query field of the HTTP action:
    Query: $filter
    Value: userPrincipalName ne ”
    It keeps failing though.

    Any help would be appreciated. Thanks!

  9. Rick says:

    I figured it out. Thanks again for all your help. I used an array to filter out blank UPNs.

  10. Ethan says:

    I had the same problem as Rick but I just cannot get passed it.

  11. Dennis says:

    Hi Ethan,

    Just like Rick mentioned. You could use a Filter Array action before the Select action to check that the UPN is not empty.

    In the criteria use something like:
    not(empty(item()?['userPrincipalName'])) is equal to true

  12. Ethan says:

    Dennis, not that problem, the whole Select Function not working. It comes up with just Body but nothing to fill it in. I think something is wrong with my HTTP Get but I followed your directions exactly: Screenshot https://imgur.com/a/PloX2We

  13. Reece says:

    I’m having the same problem as Ehtan. I can’t see any dynamic content when click on the ‘From’ field in the Select function but when I run the flow manually, I can see the raw output showing as expected.

  14. Dennis says:

    Hi Ethan & Reece,

    When you are unable to select the value field you could also use an expression in the From field.

    Try the below as an expression in the From field of the Select action:
    body('HTTP')['value']

    (It is also in the code snippet SelectInactiveUsers.json below step 4).

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.