Microsoft 365|Office|Office 365|Uncategorized

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.

Sponsored Content

Passwords Haven’t Disappeared Yet

123456. Qwerty. Iloveyou. No, these are not exercises for people who are brand new to typing. Shockingly, they are among the most common passwords that end users choose in 2021. Research has found that the average business user must manually type out, or copy/paste, the credentials to 154 websites per month. We repeatedly got one question that surprised us: “Why would I ever trust a third party with control of my network?

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 an 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
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.

Related Topics:


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (1)

One response to “Use PowerShell to Fetch Account SignIn Data from the Graph”

  1. <p>Great article and it works in my test environment. I tried running the sign in darts portion on our live environment to review registered app login and got a response that the request has been throttled. Any idea how to avoid that?</p>

Leave a Reply

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He covers Office 365 and associated technologies for and is also the lead author for the Office 365 for IT Pros eBook, updated monthly to keep pace with change in the cloud.
13 Email Threat Types to Know About Right Now

As email threats evolve and multiply, keeping track of them all—and staying protected against the many different types—becomes a complex challenge. Today, that requires more than just the traditional email gateway solution that used to be good enough.

In this eBook you will learn:

  • What are the most common and challenging email attacks for organizations?
  • How to defend against sophisticated email threats, such as spoofing, social engineering, and fraud
  • How to protect employees at the inbox level with the right technologies and security-awareness training
  • How to use a multilayered protection strategy to reduce susceptibility to email attacks and better defend your business and employees

Sponsored by: