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 = "https://graph.microsoft.com/beta/users?`$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

Read the Best Personal and Business Tech without Ads

Staying updated on what is happening in the technology sector is important to your career and your personal life but ads can make reading news, distracting. With Thurrott Premium, you can enjoy the best coverage in tech without the annoying ads.

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 }
   $Report.Add($ReportLine) 
} # 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:

BECOME A PETRI MEMBER:

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

Register
Comments (1)

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

  1. Bink141

    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?

Leave a Reply

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He covers Office 365 and associated technologies for Petri.com and is also the lead author for the Office 365 for IT Pros eBook, updated monthly to keep pace with change in the cloud.

Download this eBook!

External Sharing and Guest User Access in Microsoft 365 and Teams

his eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure. The eBook will also outline some of the major decision points across four general-purpose guest access policy scenarios for how an organization can set this up with standard licensing.

Download Now

Sponsored By