Use PowerShell to Fetch Account SignIn Data from the Graph

Tracking Down Obsolete Accounts

Last December, I wrote about how the problem of identifying obsolete guest accounts that exist in an Office 365 tenant. An increasing number of applications support Azure B2B Collaboration and create guest accounts to allow external people to access content. Teams is a great example, as are the sharing links used by SharePoint Online and OneDrive for Business.

The article describes how to use data from the Office 365 audit log and message tracking logs to figure out if guest accounts are active. The idea is that if a guest account is not used, it becomes a candidate for removal. I say only a candidate because deletion of a guest account removes access to anything that account has in a tenant, including document and folder shares.

My original script works great. To make it even better, I could exploit a Microsoft Graph API call to fetch user signin data from Azure Active Directory. To use the Graph call in PowerShell, I need to create a registered app to access the Graph (see this article to learn the basics of using PowerShell with the Graph) to fetch and unpack the data.

Fetching Signin Data from the Graph

The call to fetch signin data is something like this:

$URI = "`$select=displayName,userPrincipalName, mail, id, CreatedDateTime, signInActivity, UserType&`$top=999"
$SignInData = (Invoke-RestMethod -Uri $URI -Headers $Headers -Method Get -ContentType "application/json")

This code tells the beta version of the Graph Users API that I want a set of properties for each user, including the signinActivity.

Paging Graph Data

After executing the call, the $SignInData variable holds the returned data. One thing to be careful about is that the Graph returns data for a maximum of 999 users at a time. This is referred to as a page of data. If your tenant has more than 999 Azure Active Directory accounts (users, guests, and utility accounts such as those used by room mailboxes), the code must fetch the data a page at a time until all available records have been returned.

Processing the Graph Data

The $SignInData variable is a PowerShell array, and the Value property in the array holds the real information we are interested in. All we need to do is process each item in the array to extract the information and store it somewhere for reporting purposes. Some minor if then else checking is done to handle the fact that Azure Active Directory only stores signin data for 180 days, meaning that no signin information is returned if an account hasn’t been accessed for longer than that period.

A ForEach loop like this does the trick:

ForEach ($User in $SignInData.Value) {  
   If ($Null -ne $User.SignInActivity)     {
      $LastSignIn = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
      $DaysSinceSignIn = (New-TimeSpan $LastSignIn).Days }
   Else { #No sign in data for this user account
      $LastSignIn = "Never or > 180 days" 
      $DaysSinceSignIn = "N/A" }
   $ReportLine  = [PSCustomObject] @{          
     UPN                = $User.UserPrincipalName
     DisplayName        = $User.DisplayName
     Email              = $User.Mail
     ObjectId           = $User.Id
     Created            = Get-Date($User.CreatedDateTime) -format g      
     LastSignIn         = $LastSignIn
     DaysSinceSignIn    = $DaysSinceSignIn
     UserType           = $User.UserType }
} # End ForEach

I store report data in a PowerShell list object because it is convenient to process in several ways. To review the data, we can pipe the data to the Out-GridView cmdlet (Figure 1) and save it to a CSV file for later analysis.

Image 1 Expand
Guest Users Reported by Graph
Figure 1: Reviewing signin information retrieved from the Graph (image credit: Tony Redmond)

Because the report data is in an array, I can also cut and dice it to meet different needs. For instance, I can select all the guest accounts and sort them in descending order based on their last signin using this command:

$Report |?{$_.UserType -eq "Guest"}| Sort {$_.LastSignIn -as [DateTime]} -Descending | Format-Table DisplayName, LastSignIn, Email

DisplayName                         LastSignIn          Email
-----------                         ----------          -----
Juan Carlos Martín                  22 Apr 2020 06:02   [email protected].
Brian Desmond                       21 Apr 2020 21:21   [email protected]...
Vasil Michev                        19 Apr 2020 15:35   [email protected]

The full script is available to download from GitHub. It does not include much if any error checking, but the code is enough to prove the principle of how to use PowerShell to fetch user signin data from the Graph.

Time to Improve Some Scripts

Equipped with some new knowledge, it’s time to consider how existing scripts can be improved or new scripts built. For instance, you could send some email notifications to guests to tell them that their accounts will be removed in 14 days, or post messages to a Teams channel to report a list of obsolete accounts. Lots to do, too little time to do it.