Using PowerShell core to find stale users in Office 365 / Azure AD using the Graph API module

One of the big projects I’ve been working on this year is to translate my AD PowerShell skills to Azure AD. The really cool part of this process is the advent of the Microsoft.Graph PowerShell Module. If you haven’t taken a look at this module, I highly encourage you to.

Of the many useful scripts you can write to manage your Active Directory environment, stale users seems to be the hot topic for me at work right now. Fortunately the hottest part of said topic is how to define a ‘stale user’. The reason that is fortunate for me is that while my opinion carries weight, it is not my burden to make the final decision. So at the end of the day, whatever Security / HR decides is what I put into play. So lets take a look at how you would solve this using PowerShell and the Microsoft.Graph module.

Prerequisites

I think it is worth mentioning that while I’m doing all this work in PowerShell Core (7.1 to be specific), this should all work in Windows PowerShell as well. At least in v5.1 since the Microsoft.Graph module does support 5.1. You can find the minimum support version here.

Defining Stale

I know I mentioned that I am glad I don’t have to make this decision, but for the sake of this post, lets pick some numbers and go from there.

Though before we define some numbers, I want to mention that the sign in activity attribute (the Last sign-in value in the admin center) is in beta for the Graph API and that is even bugged, though they do suggest a workaround. Since we can’t pull the sign in activity, we will work around that with different attributes.

I’ve decided that, in my environment, a stale user is one that hasn’t changed a password for 8 months (6 months since password expired, 2 months for the pasword to expire). So this means that we need to filter for 2 attributes: Created and PasswordLastSet. Or at least if we were in AD we would. In Azure AD / Office 365 we are going to be looking at: CreatedDateTime and LastPasswordChangeDateTime. Unfortunately for us, these attributes are not filterable.

Also, this type of filtering will not work on users that have a password set to never expire.

Finding the stale users

Create the datetime object

First, lets create the right datetime object that is 8 months younger:

$date = (Get-Date).AddMonths(-8)

Get all users (unfortunately)

Then we need to query for all of our users, being careful to return the correct properties.

A quick note on the properties being returned: Get-MgUser works a bit differently than Get-AdUser in that the -Select parameter that is used to define what properties to return is not additive. Meaning that whatever properties you pass to that parameter are the only properties that are returned. I don’t like that. If you agree, check out and respond to the issue I submitted on Github with a possible improvement.

With that in mind, lets define the properties that we want to return and then get all of our users:

$properties = 'GivenName','Surname','UserPrincipalName','Id','CreatedDateTime','LastPasswordChangeDateTime'

$mgUsers = Get-MgUser -All -Select $properties

A note on permissions

I was doing my testing on a computer that I don’t typically run the Microsoft.Graph module from and was reminded that I needed to set the authorization scopes in order to get any user other than myself. You can check your current scope with:

(Get-MgContext).Scopes

You will need one of the permissions listed on the docs page, or conveniently listed here:

User.ReadBasic.All
User.Read.All
User.ReadWrite.All
Directory.Read.All
Directory.ReadWrite.All
Directory.AccessAsUser.All

By default you will not have a permission with the .All on the end of it.

To auth with additional scopes, use:

Connect-Graph -Scopes User.Read.All

Filtering for the stale ones

Getting all of the users might take a bit. I’ve got a tenant with a few thousand accounts and this typically takes about a minute, but once we’ve got all the users, we can start filtering using Where-Object:

$staleUsers = $mgUsers | Where-Object {
    $_.CreatedDateTime -lt $date -and
    $_.LastPasswordChangeDateTime -lt $date
}

Now you can see how many stale users you have:

$staleUsers.count

Hopefully its not too many, but it really doesn’t matter since you can use PowerShell to remediate with a foreach loop :)

And if you want to see the users in a table (using the $properties variable that we set earlier):

$stalUsers | Format-Table $properties

Functionitization

If you’ve read many of my other posts, you know I like PowerShell functions. So here’s one way we could do it:

Make sure you have already authenticated to Graph using Connect-Graph.

Function Get-MgStaleUsers {
    param (
        [datetime]$CreatedBefore = (Get-Date).AddMonths(-8),
        [datetime]$LastPasswordSetBefore = (Get-Date).AddMonths(-8),
        [string[]]$AdditionalProperties = @()
    )
    # these are the default properties returned by the Graph API
    $defaultProps = 'businessPhones','displayName','givenName','id','jobTitle','mail','mobilePhone','officeLocation','preferredLanguage','surname','userPrincipalName','Department'
    # these are the additional properties we need to filter with
    $filterProps = 'CreatedDateTime','LastPasswordChangeDateTime'
    $allProps = ($defaultProps + $filterProps + $AdditionalProperties) | Select -Unique
    # here's where we get the users from the API
    $mgUsers = Get-MgUser -All -Select $allProps
    # here's where we filter the users
    $mgUsers | Where-Object {
        $_.CreatedDateTime -lt $CreatedBefore -and
        $_.LastPasswordChangeDateTime -lt $LastPasswordSetBefore
    }
}

In closing

Thanks for reading my blog! I expect to be putting up some more posts relating to the Graph module in the future since I do a large share of my work in that module.