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.
List inactive users (no sign-in activity in the past 90 days) via the Graph API in #PowerAutomate pic.twitter.com/Wo06IMiFZB
— Dennis (@expiscornovus) March 4, 2022
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 😉
Flow setup
Before you start: be aware the HTTP action is part of a premium connector.
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.
3. Use the Authentication setup of the table below.
| Field | Value |
|---|---|
| Authentication | Azure Active Directory OAuth |
| Authority | https://login.microsoftonline.com |
| Tenant | GUID of your tenant |
| Audience | https://graph.microsoft.com |
| Client ID | GUID of the app you created earlier |
| Credential Type | Secret |
| Secret | Secret you just generated |
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.
That should be it for the setup.
When you run it you should see a result like below.
Happy testing!
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?
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’]
Excellent post! Thanks for the insight. I’m a little confused on where the code snippet goes in the Select action. Please advise.
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.
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.’.
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.
Hi Rick,
In that case try adding the ? character in the expressions.
UPN
item()?['userPrincipalName']lastSignInDateTime
item()?['signInActivity']?['lastSignInDateTime']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!
I figured it out. Thanks again for all your help. I used an array to filter out blank UPNs.
I had the same problem as Rick but I just cannot get passed it.
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 trueDennis, 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
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.
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).
Hi Dennis,
I have been trying to use you flow to track inactive user that have a MS license assigned to them, but with no luck …
Do you think this would be possible and can you push me in the right direction?
Hi David,
It should be possible to Select both the signInActivity and the assignedLicenses in the same query.
https://graph.microsoft.com/v1.0/users?$select=displayName,userPrincipalName,signInActivity,assignedLicensesHello Dennis
Do you how to join lsatSigenInDatet time and users start with xxxxxx, the same HTTP
like
https://graph.microsoft.com/beta/users?filter=signInActivity/lastSignInDateTime le @{addDays(utcNow(), -90)} and and endswith(userPrincipalName,’.ext@.com’)&$select=displayName,userPrincipalName,signInActivity