Inventory Microsoft 365 Guest SignInActivity with CLI for M365

tl;dr; This post describes how to use the CLI for Microsoft 365, Microsoft Graph, and PowerShell's Invoke-RestMethod to inventory all Azure AD guest user sign-in activity into a SharePoint List.  Get the full script gist on GitHub.

Update  12/24/2021: See Github issue 2889 discussion for additional details around the CLI commands used.

Microsoft 365 Guest Users

Microsoft 365 makes collaborating with external or guest users so simple. Want to collaborate with an external user in a Team, just send an invitation in email. If your tenant allows Guest users, you're done.  But how do we know if the guest accepted the invitation, logged in, or if they haven't logged in for a while?  

When a user is invited to a Team, and they log in, they are added to your Microsoft 365 tenant as a guest - a bit of an oversimplification but it suits this posts purpose (see this article for complete details). You can look at the user in Azure Active Directory and check their invitation status and their type, but  how can you query these users to get a full view of the guests in your organization?

Using the Microsoft Graph, and some filtering, you can find all the guest users in your tenant.  This means that no matter what type of tenant or subscriptions you have to Microsoft 365 you can always find the guest users in your tenant using the following Graph API call.  

httsp://$filter=userType eq 'Guest'

Very useful.  You could collect these users and have a simple process to manage guest access.  Maybe even a Flow that might ask for guest users to indicate they are still active.  But that relies on the user too much and it doesn't tell you much about the activity of the guest user.  For example, are they actively collaborating? Have they accessed your tenant recently? etc.

Get User SignInActivity with Azure AD Premium and Microsoft Graph

If you have Azure AD Premium 1, you can access sign-in activity for users. If we add a $select statement to our Graph query we can add in signInActivity. The signInActivity "provides the last interactive or non-interactive sign-in time for a specific user".   Using the lastSignInDateTime and lastNonInteractiveSignInDateTime we can detect inactive user accounts and then remove external users who have been inactive, however we define "inactive", say 90 or 180 days (that last link has great info on licensing, scopes and tenant availability).   As of this writing, you need to use the /beta API version for the signInActivity.$filter=userType eq 'Guest'&$select=id,displayName,mail,createdDateTime,externalUserState,signInActivity

Try the query above in Graph Explorer. If you get an error, you will need to add the AuditLog.Read.All permission.  

select audit perms
Select AuditLog.Read.All permission scope
consent audit perms
Consent AuditLog.Read.All permission scope

Once you add and consent to the permission scope, you should see something like the following in the Graph Explorer response.  

"@odata.context": "$metadata#users(id,displayName,mail,createdDateTime,externalUserState,signInActivity)",
"@odata.nextLink": "$filter=userType+eq+%27Guest%27&$select=id%2c+displayName%2c+mail%2ccreatedDateTime%2cexternalUserState%2c+signInActivity&$skiptoken=RFNwdAoAAQBBB...AQAAAAA",
    "value": [
    "@odata.context": "$metadata#users(id,displayName,mail,createdDateTime,externalUserState,signInActivity)/$entity",
    "id": "d456f91d-f293-4330-a804-e8ab5c0c21fb",
    "displayName": "Lebowski, Jeff",
    "mail": "",
    "createdDateTime": "2019-12-05T15:20:05Z",
    "externalUserState": "Accepted",
    "signInActivity": {
                "lastSignInDateTime": "2020-12-19T15:38:05Z",
                "lastSignInRequestId": "fa95515b-8b43-4500-abd6-c8cf0cfc0d00",
                "lastNonInteractiveSignInDateTime": "2021-08-03T19:36:05Z",
                "lastNonInteractiveSignInRequestId": "767aabb0-b662-4541-891d-eb9ac9c6e300"

Great, now we can get guest user properties with sign-in activity. However, using Graph Explorer we only get a page of guest users at a time, note that @odata.nextLink property, and we want to collect all guest information to help manage our tenant.  

Augment CLI for M365 Permission Scopes  

I am a huge fan of the CLI for Microsoft 365 and use frequently to help manage my own Microsoft 365 tenant, for demos, and other tasks.   When you initially use the CLI for M365 you have to consent to some permissions to manage SharePoint, Teams, Planner and much more!  Since our query requires AuditLog.Read.All permission we need to add this scope to the CLI for M365 (the Azure AD Enterprise App "PnP Office 365 Management Shell").  

We can use the CLI to add a scope to the existing CLI Enterprise app in our tenant - ooohhh, very meta! We'll use the m365 aad oauth2grant set command to add the AuditLog.Read.All scope to the permissions for the "PnP Office 365 Management Shell" Enterprise application.

CAUTION: If you just use the following you will clear all the current permissions. DO NOT DO THIS! Ask me how I know...

 m365 aad oauth2grant set --grantId 1eYmkh62wkaXekaZTLF0ZmIa7y6MMXBDlyA9s_URDPg  --scope "AuditLog.Read.All

When I did this, it set the single permission and removed the others. Doh! Instead, we want to append an additional scope. First, run the following to list out all of the scopes for the MS Graph. The clientId parameter is specific to your tenant. It is the ObjectId of the "PnP Office 365 Management Shell" Enterprise app in your Azure AD.

m365 aad oauth2grant list --clientId 9226e6d5-...
pnp enterprise app
PnP Enterprise App

This will get a list of all of the current scopes for the Enterprise app, and we want to get the objectId of the item returned with all of the Graph scopes. This is the objectId of the grant for the scopes. Now copy the objectId of that entry and all the scopes and build the following command. The --grantId is the objectId from the previous listing with all of the scopes. Append the AuditLog.Read.All scope to the end of the scopes list and run this command. Note: I have ellided the grantId below.

m365 aad oauth2grant set --grantId 1eYmkh62wk..._URDPg  --scope "Policy.Read.All AppCatalog.ReadWrite.All User.Invite.All Reports.Read.All Group.ReadWrite.All Directory.ReadWrite.All Directory.AccessAsUser.All Mail.ReadWrite Mail.Send IdentityProvider.ReadWrite.All ChannelMessage.Send TeamsApp.ReadWrite.All TeamsTab.ReadWrite.All ChannelSettings.ReadWrite.All TeamSettings.ReadWrite.All TeamMember.ReadWrite.All ChannelMember.ReadWrite.All ChannelMessage.Read.All TeamsAppInstallation.ReadWriteForUser Team.Create Tasks.ReadWrite AuditLog.Read.All" 

Run m365 aad oauth2grant list --clientId 9226e6d5-... again to check that the scope is now included in the scopes, or you can check in the list of scopes for the Enterprise app.

We can also run the following CLI command to test the new access token (Windows users replace | pbcopy with | clip). This will create a new access token for the Graph using the scopes we just update.

m365 util accesstoken get -r --new --output text | pbcopy

Open a browser to and paste the token into the top of the page. You should see the scopes now include AuditLog.Read.All like below. If you have issues, you may need to run m365 logout and m365 login again.

Audit perms token
Access Token

Query Guest SignInAcitvity with CLI for Microsoft 365 and PowerShell

Now for the inventory and the meat of our script. We know the Graph query and we have the permissions needed for the CLI. Make an initial query for all of the guest user signInActivity with $graphResult = Get-GuestUserSignInActivity . This will call a function with no parameters and get our first set of users. The AuditLog information is not one of the 450+ CLI commands, but we can use the CLI to get an access token and call the Graph with Invoke-RestMethod. This gives us our first set of users.

function Get-GuestUserSignInActivity
        [parameter(Mandatory = $false)]
        $queryUrl = "$($GRAPH_API_BASE)$($GRAPH_API_VERSION)/users?`$filter=userType eq 'Guest'&`$select=id,displayName,mail,createdDateTime,externalUserState,signInActivity"
    # Use CLI to get an access token to Graph API for the users sign in activity
    $accessToken = m365 util accesstoken get -r $($GRAPH_API_BASE) --new --output text
    $headers = @{
        Authorization = "Bearer $accessToken"
        'Content-Type' = "application/json"
    $signInActivityResponse = Invoke-RestMethod -Uri $queryUrl -Headers $headers -Method Get   
    return $signInActivityResponse

Track the Current User Properties

For each user, we create a [PSCustomObject] and append some of the properties returned. If the user has not accepted the invitation, then we set a default value of "Invited".

      $currentUser = [pscustomobject]@{
            ObjectId = $
            DisplayName = $_.displayName
            Mail = $_.mail
            CreatedDateTime = $_.createdDateTime
            UserState = ($null -ne $_.externalUserState) ? $_.externalUserState : "Invited" # Provide a default value if the externalUserState is null

Since not all guests have logged in, we need to sanitize the signInActivity and add those properties to the current user.

# Sanitize and add the signInActivity 
if ($_.signInActivity -eq $null)
   $currentUser | Add-Member -Type NoteProperty -Name 'LastSignInDateTime' -Value $(Get-Date 01-01-1999)
   $currentUser | Add-Member -Type NoteProperty -Name 'LastNonInteractiveSignInDateTime' -Value $(Get-Date 01-01-1999)
   $lastSignInDateTime = ($_.signInActivity.lastSignInDateTime -lt (Get-Date $DEFAULT_UNACCEPTED_DATE)) ? $(Get-Date $DEFAULT_UNACCEPTED_DATE) : $_.signInActivity.lastSignInDateTime
   $lastNonInteractiveSignInDateTime = ($_.signInActivity.lastNonInteractiveSignInDateTime -lt (Get-Date $DEFAULT_UNACCEPTED_DATE)) ? $(Get-Date $DEFAULT_UNACCEPTED_DATE) : $_.signInActivity.lastNonInteractiveSignInDateTime

   $currentUser | Add-Member -Type NoteProperty -Name 'LastSignInDateTime' -Value $($lastSignInDateTime)

   $currentUser | Add-Member -Type NoteProperty -Name 'LastNonInteractiveSignInDateTime' -Value $($lastNonInteractiveSignInDateTime)

Add Guest Team Memberships

The script enables collecting the users Team memberships using the list joinedTeams end point from Microsoft Graph. This could enable notifying Team owners of guest removal, or posting an Adaptive Card to a Team channel indicating the user has been removed from the tenant, or any other update that might be needed.

# add Teams if commmand line switch provided
$teams = $includeTeams ? (Get-JoinedTeams $ : "Skipped Teams Check"     

$currentUser | Add-Member -Type NoteProperty -Name 'MemberTeams' -Value $teams                

If the initial query of the Graph had more users, then the @odata.nextLink property is returned from the Graph with a $skipToken to get the next page of information. Check that the property exists and call Get-GuestUserSignInActivity with @odata.nextLink as the new query URL. Note the single quotes around the property name to ecape this for PowerShell. As long as there are more records, we'll keep appending to the list of guest users.

$moreEntries = ($null -ne $graphResult.'@odata.nextLink')
if ($moreEntries)
    $graphResult = Get-GuestUserSignInActivity -queryUrl $graphResult.'@odata.nextLink'

Add Guest Information to a SharePoint List

Finally, we have all of the guest user information and we want to write it to a SharePoint list. This is really an "upsert" operation, since we may want to run the script many times. If we use the user ObjectId as a unique identifier, we can simply iterate over the guest users and update a SharePoint list.

# Upsert the list of users to the SP site / list
$guestUsers | ForEach-Object {
    $listParams = @{
        webUrl = $WebUrl 
        listName = $ListName
        displayName = $_.DisplayName
        objectId = $_.ObjectId
        email = $_.Mail
        domain = $_.Mail.Split('@')[1]
        memberTeams = $_.MemberTeams
        createdDateTime = $_.CreatedDateTime
        userState = $_.UserState
        lastSignInDateTime = $_.LastSignInDateTime
        lastNonInteractiveSignInDateTime = $_.LastNonInteractiveSignInDateTime
Add-GuestUserManagementListItem @listParams

The Add-GuestUserManagementListItem function takes the splatted parameters, queries for the user with m365 spo listitem list, and uses the CLI's m365 spo listitem add or m365 spo listitem set commmand based on the existence of the ObjectId for the user.


We now have a complete inventory of our Guest user sign in activity and we can run this regularly to help manage our Guest users.  Using the Microsoft Graph and signInActivity can greatly reduce the risks of Guest user accounts in your tenant.  Combined with Conditional Access policies and other configurations you can ensure MFA or even restrict sharing options from Teams.  If you don't have Azure AD Premium 1, you can at least get all the guest users and use that information to manage guest users.

While I chose to use PowerShell and the CLI for Microsoft 365, this is Microsoft Graph, so you could use PowerShell+Azure Functions, a .NET Core project, Python, and others as well.  The Graph Changelog might be a better option for very large organizations with a significant number of users.

Most of my posts are reminders to myself, or ways to reinforce some of the things I have learned, and this post is no exception.   As always, HTH and feel free to let me know in the comments below or Tweet at me!

Tweet Post Update Email

My name is Pete Skelly. I write this blog. I am the VP of Technology at ThreeWill, LLC in Alpharetta, GA.

cli m365dev azure graph powershell sharepoint
comments powered by Disqus