Detect Underused Azure AD Accounts (with Expensive Licenses)

Update the Microsoft 365 Licensing Report Script to Find Underused Accounts

In October 2021, I wrote about how to use the Microsoft Graph PowerShell SDK to create a licensing report for a Microsoft 365 tenant. That report lists the licenses assigned to each Azure AD account together with any disabled service plans for those licenses. It’s a valuable piece of information to help tenants manage license costs.

But we can do better. At least, that’s what some readers think. They’d like to know if people use their assigned licenses so that they can remove expensive licenses from accounts that aren’t active. One way to approach the problem is to use the Microsoft 365 User Activity Report script to identify people who haven’t been active in Exchange Online. SharePoint Online, Teams, OneDrive for Business, and Yammer over the last 180 days. The report already includes an assessment of whether an account is in use, so all you need to do is find those who aren’t active and consider removing their licenses.

Another solution to the problem is to update the licensing report script. To do this, I made several changes to the script (the updated version is available from GitHub).

Filtering for Licensed Accounts

The first change is to the filter used with the Get-MgUser cmdlet. The new filter selects only member accounts that have licenses. Previously, I selected all member accounts, but now we’re interested in chasing down underused licensed accounts. Here’s the command I used:

[Array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All -Property signInActivity | Sort-Object DisplayName

The filter applied to Get-MgUser finds Azure AD member accounts with at least one license. The command also retrieves the values of the signInActivity property for each account. This property holds the date and time for an account’s last interactive and non-interactive sign-ins. Here’s what the data for an account looks like:

LastNonInteractiveSignInDateTime  : 27/09/2022 13:04:58
LastNonInteractiveSignInRequestId : bcd2d562-76f0-4d29-a266-942f7ee31a00
LastSignInDateTime                : 11/05/2022 12:19:18
LastSignInRequestId               : 3f691116-5e0a-4c4c-a3a9-aecb3ae99800
AdditionalProperties              : {}

The last non-interactive sign-in might be something like a synchronization operation performed by the OneDrive sync client. I’m not too interested in these sign-in activities as I want to know about licensed accounts that aren’t taking full advantage of their expensive licenses. Hence, we focus on the timestamp for the last interactive sign-in.

Calculating How Long Since an Account Sign-in

To detect an underused account, we need to define how to recognize such an account. To keep things simple, I define an underused account as being more that hasn’t signed in interactively for over 60 days. An account in this category costs $23/month if it holds an Office 365 E3 license while one assigned an E5 license costs $38/month. And that’s not taking any add-on licenses into account. At $30/month, we’ve already paid $60 for an underused account when it matches our criterion.

The script checks to see if any Azure AD sign-in information is available for the account (i.e., the account has signed in at least once). If it does, we extract the timestamp for the last interactive sign-in and compute how many days it is since that time. If not, we mark the account appropriately.

# Calculate how long it's been since someone signed in
  If ([string]::IsNullOrWhiteSpace($User.SignInActivity.LastSignInDateTime) -eq $False) {
    [datetime]$LastSignInDate = $User.SignInActivity.LastSignInDateTime
    $DaysSinceLastSignIn = ($CreationDate - $LastSignInDate).Days
    $LastAccess = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
    If ($DaysSinceLastSignIn -gt 60) { $UnusedAccountWarning = ("Account unused for {0} days - check!" -f $DaysSinceLastSignIn) }
  }
  Else {
    $DaysSinceLastSignIn = "Unknown"
    $UnusedAccountWarning = ("Unknown last sign-in for account")
    $LastAccess = "Unknown"
  }

Note that it can take a couple of minutes before Azure AD updates the last interactive timestamp for an account. This is likely due to caching and the need to preserve service resources.

Reporting Underused Accounts

The last change is to the output routine where the script now reports the percentage of underused accounts that it finds. Obviously, it’s not ideal if this number is more than a few percent.

I usually pipe the output of reports to the Out-GridView cmdlet to check the data. Figure 1 shows the output from my tenant. Several underused accounts are identified, which is what I expect given the testing and non-production usage pattern within the tenant. Another advantage of Out-GridView is that it’s easy to sort the information to focus in on problem items as seen here.

Customizing the Output

Seeing that the script is PowerShell, it’s easy to adjust the code to meet the requirements of an organization. Some, for instance, might have a higher tolerance level before they consider an account underutilized and some might be more restrictive. Some might like to split the report up into departments and send the underused accounts found for each department to its manager for review. It’s PowerShell, so go crazy and make the data work for you.

---

Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.