Skip to Main Content

Understand your Azure Active Directory SignIns with KQL

Ruben Zimmermann

Infrastructure Architect, SIG

When Azure AD is configured to record Sign-In activity, Kusto KQL can be used to gain valuable insights. This blog walks through common needs and shows how to visualize them in SquaredUp.

Ruben Zimmermann is an Infrastructure Architect at a large manufacturing company who likes Azure, KQL, PowerShell and, still, SCOM. In this article, he shares the Azure Active Directory dashboard he created in SquaredUp to analyze sign-in activity and walks us through how to use KQL to pull and visualize valuable data.

This Azure AD dashboard covers the following and more:

Introduction

There are some great benefits of using KQL with Azure AD to help you understand your sign-ins.

Having Azure AD as identity provider offers convenient Single Sign On experience for users. Plus, you get increased security due to MFA and other identity protection features.

Enabling auditing and storing the results in a Log Analytics Workplace allows detailed analysis about application usage, sign-in experience, user behavior and overseeing guest activity in your tenant.

Enabling Diagnostic Settings for Azure Active Directory

Shortly after enabling logging, events are logged in the SigninLogs table.

Note: Nearly all queries I use in this blog are against the table shown in the image below.

Azure AD LogAnalytics Table

(Find links about learning KQL in the appendix at the end of the blog. Also, suggestions for better queries are appreciated! 😉)

Configuration & Code

I created a powerful Azure AD dashboard in SquaredUp Dashboard Server and throughout the blog, I’ll explain most of the visualizations in detail. The queries are written in KQL.

Unique SignIns Total

This first donut diagram shows the proportion of Guests to Members (here called Employees) with concrete numbers. Each Guest or Member login is only counted once.

Unique Sign-ins total

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| extend UserLoginType = iif(UserType == "Member","Employees","Guests")

| project UserLoginType, UserPrincipalName

| summarize dcount(UserPrincipalName) by UserLoginType

Azure -Log Analytics (Donut) is the best fit here.

Unique SignIns total 1 of 2

Unique SignIns total 2 of 2

Unique Sign Ins over Time

This diagram shows Guests and Members (here Employees) sign in count, summarized by day. Each day counts individually.

Unique SignIns over time

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| extend UserLoginType = iif(UserType == "Member","Employee","Guest")

| project TimeGenerated, UserLoginType,UserPrincipalName

| summarize Employees = dcountif(UserPrincipalName,UserLoginType=="Employee"), Guests = dcountif(UserPrincipalName,UserLoginType=="Guest") by bin(TimeGenerated, 1d)

Azure – Log Analytics (Line Graph) is the visualization choice here.

Unique SignIns over time 1 of 2

Unique SignIns over time 2 of 2

Operating Systems

Used operating systems are mostly correctly identified and this visualization shows clearly where Azure AD applications are consumed.

Operating systems

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| extend OSStrg = iif(isempty(tostring(DeviceDetail.operatingSystem)),"Unknown OS",tostring(DeviceDetail.operatingSystem))

| extend OSType = case (OSStrg matches regex "Windows", "Windows"
  , OSStrg matches regex "iOS", "iOS"
  , OSStrg matches regex "MacOs", "MacOs"
  , OSStrg matches regex "Android", "Android"
  , OSStrg matches regex "Linux", "Linux", "Unknown OS"
)

I picked the Azure – Log Analytics (Bar Graph) for this visualization.

Operating Systems 1 of 2

Operating Systems 2 of 2

Password Issues

Users failing to login due to password issues or other errors are shown here. Only the last day is considered in the query.

Password Issues

For the donut, use the following KQL query:

SigninLogs

| where TimeGenerated between (ago(1d) .. now())

| where ResultType in(50144,50133,50126,50053)

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| summarize arg_max(TimeGenerated, *) by UserPrincipalName

| extend IssueType = case (
  ResultType == 50126, "Invalid username or bad password",
  ResultType == 50133, "Session invalid due to recent password change",
  ResultType == 50144, "Password expired",
  ResultType == 50133, "Account locked", "Unknown"
  )

| where IssueType !in("Unknown","Session invalid due to recent password change","Invalid username or bad password")

| extend readableDate = format_datetime(TimeGenerated,"yyyy-MM-dd HH:mm")

| summarize Users = dcount(UserPrincipalName) by IssueType

The table overview is built with the lines below.

SigninLogs

| where TimeGenerated between (ago(1d) .. now())

| where ResultType in(50144,50133,50126,50053)

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| summarize arg_max(TimeGenerated, *) by UserPrincipalName

| extend IssueType = case (
  ResultType == 50126, "Invalid username or bad password",
  ResultType == 50133, "Session invalid due to recent password change",
  ResultType == 50144, "Password expired",
  ResultType == 50133, "Account locked", "Unknown"
  )

| where IssueType !in("Unknown","Session invalid due to recent password change","Invalid username or bad password")

| extend readableDate = format_datetime(TimeGenerated,"yyyy-MM-dd HH:mm")

| extend Day = format_datetime(TimeGenerated,"yyyy-MM-dd")

| extend Time = format_datetime(TimeGenerated,"HH:mm")

| summarize by IssueType, readableDate, UserDisplayName,UserID=onPremisesSamAccountName, Day, Time

Risky Sign-ins

One of Azure ADs most famous protection features is Risky Sign-Ins. An algorithm here checks for possible malicious sign in attempts that occur when credential theft has occurred.

I store this information in the AADUserRiskEvents table.

Risky SignIns

AADUserRiskEvents

| where TimeGenerated between (ago(1d) .. now())

| where RiskState != "dismissed"

| where RiskState != "remediated"

| extend readableDate = format_datetime(TimeGenerated,"yyyy-MM-dd HH:mm")

| extend Day = format_datetime(TimeGenerated,"yyyy-MM-dd")

| extend Time = format_datetime(TimeGenerated,"HH:mm")

| summarize arg_max(TimeGenerated, *) by UserPrincipalName

| project User = replace_string(UserPrincipalName,"@mydomain.com",""), readableDate, RiskLevel, RiskEventType, RiskState, tostring(Location.city), Day, Time

I used the Azure – Log Analytics (grid) for the table. Conditional formatting helps us spot the most serious events.

Risky SignIns 1 of 2

Risky SignIns 2 of 2

MFA Successful Sign Ins

Details about usage and preference of MFA can be obtained from the Sign-In logs.

MFA Successful SignIns

SigninLogs

| where TimeGenerated between (startofday(ago(7d)) .. now())

| where ResultType == 0 and ConditionalAccessStatus == 'success' and Status.additionalDetails == "MFA completed in Azure AD" and ConditionalAccessPolicies[0].result == "success" and parse_json(tostring(ConditionalAccessPolicies[0].enforcedGrantControls))[0] == "Mfa"

| where UserType == "Member"

| project Identity, MFAType = iif(isempty(tostring(MfaDetail.authMethod)),"unknown",tostring(MfaDetail.authMethod))

| summarize TotalUsers = dcount(Identity) by MFAType

| sort by TotalUsers desc

Top 5 Non-MS Applications

I also wanted to retrieve usage trends for non-Microsoft applications. Microsoft recently released a website which lists many of its applications. Unfortunately, it doesn’t list them all and it’s a static website. But I created a visualization for the top 5 non-MS apps used.

See: https://learn.microsoft.com/en-us/troubleshoot/azure/active-directory/verify-first-party-apps-sign-in

Top non-MS applications

let MicrosoftApps = datatable (AppName: string, AppId: string )

[
  "ACOM Azure Website","23523755-3a2b-41ca-9315-f81f3f566a95",

  "AEM-DualAuth","69893ee3-dd10-4b1c-832d-4870354be3d8",

  "ASM Campaign Servicing","0cb7b9ec-5336-483b-bc31-b15b5788de71",

  "Azure Advanced Threat Protection","7b7531ad-5926-4f2d-8a1d-38495ad33e17",

  "Azure Data Lake","e9f49c6b-5ce5-44c8-925d-015017e9f7ad",

  "Azure Lab Services Portal","835b2a73-6e10-4aa5-a979-21dfda45231c",

  "Azure Portal","c44b4083-3bb0-49c1-b47d-974e53cbdf3c",

  "AzureSupportCenter","37182072-3c9c-4f6a-a4b3-b3f91cacffce",

  "Bing","9ea1ad79-fdb6-4f9a-8bc3-2b70f96e34c7",

  "CPIM Service","bb2a2e3a-c5e7-4f0a-88e0-8e01fd3fc1f4",

  "CRM Power BI Integration","e64aa8bc-8eb4-40e2-898b-cf261a25954f",

  "Dataverse","00000007-0000-0000-c000-000000000000",

  "Enterprise Roaming and Backup","60c8bde5-3167-4f92-8fdb-059f6176dc0f",

  "IAM Supportability","a57aca87-cbc0-4f3c-8b9e-dc095fdc8978",

  "IrisSelectionFrontDoor","16aeb910-ce68-41d1-9ac3-9e1673ac9575",

  "MCAPI Authorization Prod","d73f4b35-55c9-48c7-8b10-651f6f2acb2e",

  "Media Analysis and Transformation Service","944f0bd1-117b-4b1c-af26-804ed95e767e",

  "Media Analysis and Transformation Service2","0cd196ee-71bf-4fd6-a57c-b491ffd4fb1e", 

  "O365 Suite UX","4345a7b9-9a63-4910-a426-35363201d503",

  "Office Delve","94c63fef-13a3-47bc-8074-75af8c65887a",

  "Office Online Add-in SSO","93d53678-613d-4013-afc1-62e9e444a0a5",

  "Office Online Client AAD- Augmentation Loop","2abdc806-e091-4495-9b10-b04d93c3f040",

  "Office Online Client AAD- Loki","b23dd4db-9142-4734-867f-3577f640ad0c",

  "Office Online Client AAD- Maker","17d5e35f-655b-4fb0-8ae6-86356e9a49f5",

  "Office Online Client MSA- Loki","b6e69c34-5f1f-4c34-8cdf-7fea120b8670",

  "Office Online Core SSO","243c63a3-247d-41c5-9d83-7788c43f1c43",

  "Office Online Search","a9b49b65-0a12-430b-9540-c80b3332c127",

  "Office.com","4b233688-031c-404b-9a80-a4f3f2351f90",

  "Office365 Shell WCSS-Client","89bee1f7-5e6e-4d8a-9f3d-ecd601259da7",

  "OfficeClientService","0f698dd4-f011-4d23-a33e-b36416dcb1e6",

  "OfficeHome","4765445b-32c6-49b0-83e6-1d93765276ca",

  "OfficeShredderWacClient","4d5c2d63-cf83-4365-853c-925fd1a64357",

  "OMSOctopiPROD","62256cef-54c0-4cb4-bcac-4c67989bdc40",

  "OneDrive SyncEngine","ab9b8c07-8f02-4f72-87fa-80105867a763",

  "OneNote","2d4d3d8e-2be3-4bef-9f87-7875a61c29de",

  "Outlook Mobile","27922004-5251-4030-b22d-91ecd9a37ea4",

  "Partner Customer Delegated Admin Offline Processor","a3475900-ccec-4a69-98f5-a65cd5dc5306",

  "Password Breach Authenticator","bdd48c81-3a58-4ea9-849c-ebea7f6b6360",

  "Power BI Service","00000009-0000-0000-c000-000000000000",

  "SharedWithMe","ffcb16e8-f789-467c-8ce9-f826a080d987",

  "SharePoint Online Web Client Extensibility","08e18876-6177-487e-b8b5-cf950c1e598c",

  "Signup","b4bddae8-ab25-483e-8670-df09b9f1d0ea",

  "Skype for Business Online","00000004-0000-0ff1-ce00-000000000000",

  "Sway","905fcf26-4eb7-48a0-9ff0-8dcc7194b5ba",

  "Universal Store Native Client","268761a2-03f3-40df-8a8b-c3db24145b6b",

  "Vortex [wsfed enabled]","5572c4c0-d078-44ce-b81c-6cbf8d3ed39e",

  "Yammer","00000005-0000-0ff1-ce00-000000000000",

  "Yammer Web","c1c74fed-04c9-4704-80dc-9f79a2e515cb",

  "Yammer Web Embed","e1ef36fd-b883-4dbf-97f0-9ece4b576fc6",

  "Windows Sign In","38aa3b87-a06d-4817-b275-7a316988d93b",

  "PowerApps - apps.powerapps.com","3e62f81e-590b-425b-9531-cad6683656cf",

  "make.powerapps.com","a8f7a65c-f5ba-4859-b2d6-df772c264e9d",

  "Microsoft Azure Information Protection","c00e9d32-3c8d-4a7d-832b-029040e7db99",

  "Microsoft Edge Enterprise New Tab Page","d7b530a4-7680-4c23-a8bf-c52c121d2e87",

  "Microsoft Account Controls V2","7eadcef8-456d-4611-9480-4fff72b8b9e2",

  "SharePoint Online Client Extensibility Web Application Principal","f7f708bc-b136-4073-b000-e730786c986e",

  "Power BI Desktop","7f67af8a-fedc-4b08-8b4e-37c4d127b6cf",

  "Office Online Print SSO","3ce44149-e365-40e4-9bb4-8c0ecb710fe6",

  "PowerApps","4e291c71-d680-4d0e-9640-0a3358e31177",

  "My Profile","8c59ead7-d703-4a27-9e55-c96a0054c8d2",

  "Apple Internet Accounts","f8d98a96-0999-43f5-8af3-69971c7bb423",

  "My Signins","19db86c3-b2b9-44cc-b339-36da233a3be2",

  "My Apps","2793995e-0a7d-40d7-bd35-6968ba142197"

];

let MicrosoftAppIdList = MicrosoftApps | summarize MicrosoftAppIds = make_list(AppId);

let AllMembers = materialize(SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserType == "Member"

| where UserPrincipalName matches regex @"\w+@\w+\.\w+");

AllMembers

| project TimeGenerated, Location, AppDisplayName, LoginCity=tostring(LocationDetails.city), UserPrincipalName, OSType = tostring(DeviceDetail.operatingSystem), AppId, UserType

| where tostring(AppId) !in (MicrosoftAppIdList)

| where AppDisplayName !startswith("Microsoft")

| where AppDisplayName !startswith("Office")

| where AppDisplayName !startswith("Windows")

| summarize UserCount = dcount(UserPrincipalName) by AppDisplayName

| sort by UserCount desc

| top 5 by UserCount

Top 5 MS Applications (OneDrive, Teams, SharePoint excluded)

This query focusses on Microsoft Applications. As every user uses OneDrive, SharePoint, and Teams those are excluded.

Top 5 MS applications

let MicrosoftApps = datatable (AppName: string, AppId: string )

[

  "ACOM Azure Website","23523755-3a2b-41ca-9315-f81f3f566a95",

  "AEM-DualAuth","69893ee3-dd10-4b1c-832d-4870354be3d8",

  "ASM Campaign Servicing","0cb7b9ec-5336-483b-bc31-b15b5788de71",

  "Azure Advanced Threat Protection","7b7531ad-5926-4f2d-8a1d-38495ad33e17",

  "Azure Data Lake","e9f49c6b-5ce5-44c8-925d-015017e9f7ad",

  "Azure Lab Services Portal","835b2a73-6e10-4aa5-a979-21dfda45231c",

  "Azure Portal","c44b4083-3bb0-49c1-b47d-974e53cbdf3c",

  "AzureSupportCenter","37182072-3c9c-4f6a-a4b3-b3f91cacffce",

  "Bing","9ea1ad79-fdb6-4f9a-8bc3-2b70f96e34c7",

  "CPIM Service","bb2a2e3a-c5e7-4f0a-88e0-8e01fd3fc1f4",

  "CRM Power BI Integration","e64aa8bc-8eb4-40e2-898b-cf261a25954f",

  "Dataverse","00000007-0000-0000-c000-000000000000",

  "Enterprise Roaming and Backup","60c8bde5-3167-4f92-8fdb-059f6176dc0f",

  "IAM Supportability","a57aca87-cbc0-4f3c-8b9e-dc095fdc8978",

  "IrisSelectionFrontDoor","16aeb910-ce68-41d1-9ac3-9e1673ac9575",

  "MCAPI Authorization Prod","d73f4b35-55c9-48c7-8b10-651f6f2acb2e",

  "Media Analysis and Transformation Service","944f0bd1-117b-4b1c-af26-804ed95e767e",

  "Media Analysis and Transformation Service2","0cd196ee-71bf-4fd6-a57c-b491ffd4fb1e", 

  "O365 Suite UX","4345a7b9-9a63-4910-a426-35363201d503",

  "Office Delve","94c63fef-13a3-47bc-8074-75af8c65887a",

  "Office Online Add-in SSO","93d53678-613d-4013-afc1-62e9e444a0a5",

  "Office Online Client AAD- Augmentation Loop","2abdc806-e091-4495-9b10-b04d93c3f040",

  "Office Online Client AAD- Loki","b23dd4db-9142-4734-867f-3577f640ad0c",

  "Office Online Client AAD- Maker","17d5e35f-655b-4fb0-8ae6-86356e9a49f5",

  "Office Online Client MSA- Loki","b6e69c34-5f1f-4c34-8cdf-7fea120b8670",

  "Office Online Core SSO","243c63a3-247d-41c5-9d83-7788c43f1c43",

  "Office Online Search","a9b49b65-0a12-430b-9540-c80b3332c127",

  "Office.com","4b233688-031c-404b-9a80-a4f3f2351f90",

  "Office365 Shell WCSS-Client","89bee1f7-5e6e-4d8a-9f3d-ecd601259da7",

  "OfficeClientService","0f698dd4-f011-4d23-a33e-b36416dcb1e6",

  "OfficeHome","4765445b-32c6-49b0-83e6-1d93765276ca",

  "OfficeShredderWacClient","4d5c2d63-cf83-4365-853c-925fd1a64357",

  "OMSOctopiPROD","62256cef-54c0-4cb4-bcac-4c67989bdc40",

  "OneDrive SyncEngine","ab9b8c07-8f02-4f72-87fa-80105867a763",

  "OneNote","2d4d3d8e-2be3-4bef-9f87-7875a61c29de",

  "Outlook Mobile","27922004-5251-4030-b22d-91ecd9a37ea4",

  "Partner Customer Delegated Admin Offline Processor","a3475900-ccec-4a69-98f5-a65cd5dc5306",

  "Password Breach Authenticator","bdd48c81-3a58-4ea9-849c-ebea7f6b6360",

  "SharedWithMe","ffcb16e8-f789-467c-8ce9-f826a080d987",

  "SharePoint Online Web Client Extensibility","08e18876-6177-487e-b8b5-cf950c1e598c",

  "Signup","b4bddae8-ab25-483e-8670-df09b9f1d0ea",

  "Skype for Business Online","00000004-0000-0ff1-ce00-000000000000",

  "Sway","905fcf26-4eb7-48a0-9ff0-8dcc7194b5ba",

  "Universal Store Native Client","268761a2-03f3-40df-8a8b-c3db24145b6b",

  "Vortex [wsfed enabled]","5572c4c0-d078-44ce-b81c-6cbf8d3ed39e", 

  "Windows Sign In","38aa3b87-a06d-4817-b275-7a316988d93b", 

  "Microsoft Edge Enterprise New Tab Page","d7b530a4-7680-4c23-a8bf-c52c121d2e87",

  "Microsoft Account Controls V2","7eadcef8-456d-4611-9480-4fff72b8b9e2",

  "SharePoint Online Client Extensibility Web Application Principal","f7f708bc-b136-4073-b000-e730786c986e", 

  "Office Online Print SSO","3ce44149-e365-40e4-9bb4-8c0ecb710fe6",

  "My Profile","8c59ead7-d703-4a27-9e55-c96a0054c8d2",

  "Apple Internet Accounts","f8d98a96-0999-43f5-8af3-69971c7bb423",

  "My Signins","19db86c3-b2b9-44cc-b339-36da233a3be2",

  "My Apps","2793995e-0a7d-40d7-bd35-6968ba142197"

];

let MicrosoftAppIdList = MicrosoftApps | summarize MicrosoftAppIds = make_list(AppId);

let AllMembers = materialize(SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserType == "Member"

| where UserPrincipalName matches regex @"\w+@\w+\.\w+");

AllMembers

| project TimeGenerated, Location, AppDisplayName, LoginCity=tostring(LocationDetails.city), UserPrincipalName, OSType = tostring(DeviceDetail.operatingSystem), AppId, UserType

| where tostring(AppId) !in (MicrosoftAppIdList)

| where AppDisplayName matches regex "(?i)Power|make|yammer|dataverse"

| where AppDisplayName !contains("PowerShell")

| extend AppType = case (AppId in("3e62f81e-590b-425b-9531-cad6683656cf", "a8f7a65c-f5ba-4859-b2d6-df772c264e9d", "4e291c71-d680-4d0e-9640-0a3358e31177","065d9450-1e87-434e-ac2f-69af271549ed"), "Power Apps"
  , AppDisplayName matches regex "Power BI|PowerBI" , "Power BI"
  , AppDisplayName has "Yammer", "Yammer"
  , AppDisplayName
  )

| summarize UserCount = dcount(UserPrincipalName) by  AppType

| top 10 by UserCount

| sort by UserCount desc

Guest Users Total

This SquaredUp visualization shows the total number of individual Guest accounts who are signed in.

Guest users total

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| where UserType == "Guest"

| project TimeGenerated, UserPrincipalName

| summarize DomainCount = dcount(UserPrincipalName)

Guest users total 1 of 2

Guest users total 2 of 2

Guest Domains Total

This query groups guests by their domain name and counts them as Guest-Domain.

Guest domains total

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| where UserType == "Guest"

| project TimeGenerated, UserPrincipalName

| extend MailDomain = replace_string(extract("@\\S+$",0,UserPrincipalName),"@","")

| summarize DomainCount = dcount(MailDomain)

Top 5 Users of Guest Domains

This next chart shows which Guest Domains show up most.

Top 5 guest user domains

SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| where UserPrincipalName !endswith("mydomain.com")

| where UserType == "Guest"

| project TimeGenerated, AppDisplayName, UserPrincipalName

| extend MailDomain = replace_string(extract("@\\S+$",0,UserPrincipalName),"@","")

| summarize GuestCount = dcount(UserPrincipalName) by MailDomain

| top 5 by GuestCount

Top 5 Applications by Guests

The final visualization – a donut chart – shows which applications are used most by guests. I excluded the most popular Microsoft Applications.

Top 5 app by guest

let MicrosoftApps = datatable (AppName: string, AppId: string )

[

  "ACOM Azure Website","23523755-3a2b-41ca-9315-f81f3f566a95",

  "AEM-DualAuth","69893ee3-dd10-4b1c-832d-4870354be3d8",

  "ASM Campaign Servicing","0cb7b9ec-5336-483b-bc31-b15b5788de71",

  "Azure Advanced Threat Protection","7b7531ad-5926-4f2d-8a1d-38495ad33e17",

  "Azure Data Lake","e9f49c6b-5ce5-44c8-925d-015017e9f7ad",

  "Azure Lab Services Portal","835b2a73-6e10-4aa5-a979-21dfda45231c",

  "Azure Portal","c44b4083-3bb0-49c1-b47d-974e53cbdf3c",

  "AzureSupportCenter","37182072-3c9c-4f6a-a4b3-b3f91cacffce",

  "Bing","9ea1ad79-fdb6-4f9a-8bc3-2b70f96e34c7",

  "CPIM Service","bb2a2e3a-c5e7-4f0a-88e0-8e01fd3fc1f4",

  "CRM Power BI Integration","e64aa8bc-8eb4-40e2-898b-cf261a25954f",

  "Dataverse","00000007-0000-0000-c000-000000000000",

  "Enterprise Roaming and Backup","60c8bde5-3167-4f92-8fdb-059f6176dc0f",

  "IAM Supportability","a57aca87-cbc0-4f3c-8b9e-dc095fdc8978",

  "IrisSelectionFrontDoor","16aeb910-ce68-41d1-9ac3-9e1673ac9575",

  "MCAPI Authorization Prod","d73f4b35-55c9-48c7-8b10-651f6f2acb2e",

  "Media Analysis and Transformation Service","944f0bd1-117b-4b1c-af26-804ed95e767e",

  "Media Analysis and Transformation Service2","0cd196ee-71bf-4fd6-a57c-b491ffd4fb1e",

  "O365 Suite UX","4345a7b9-9a63-4910-a426-35363201d503",

  "Office Delve","94c63fef-13a3-47bc-8074-75af8c65887a",

  "Office Online Add-in SSO","93d53678-613d-4013-afc1-62e9e444a0a5",

  "Office Online Client AAD- Augmentation Loop","2abdc806-e091-4495-9b10-b04d93c3f040",

  "Office Online Client AAD- Loki","b23dd4db-9142-4734-867f-3577f640ad0c",

  "Office Online Client AAD- Maker","17d5e35f-655b-4fb0-8ae6-86356e9a49f5",

  "Office Online Client MSA- Loki","b6e69c34-5f1f-4c34-8cdf-7fea120b8670",

  "Office Online Core SSO","243c63a3-247d-41c5-9d83-7788c43f1c43",

  "Office Online Search","a9b49b65-0a12-430b-9540-c80b3332c127",

  "Office.com","4b233688-031c-404b-9a80-a4f3f2351f90",

  "Office365 Shell WCSS-Client","89bee1f7-5e6e-4d8a-9f3d-ecd601259da7",

  "OfficeClientService","0f698dd4-f011-4d23-a33e-b36416dcb1e6",

  "OfficeHome","4765445b-32c6-49b0-83e6-1d93765276ca",

  "OfficeShredderWacClient","4d5c2d63-cf83-4365-853c-925fd1a64357",

  "OMSOctopiPROD","62256cef-54c0-4cb4-bcac-4c67989bdc40",

  "OneDrive SyncEngine","ab9b8c07-8f02-4f72-87fa-80105867a763",

  "OneNote","2d4d3d8e-2be3-4bef-9f87-7875a61c29de",

  "Outlook Mobile","27922004-5251-4030-b22d-91ecd9a37ea4",

  "Partner Customer Delegated Admin Offline Processor","a3475900-ccec-4a69-98f5-a65cd5dc5306",

  "Password Breach Authenticator","bdd48c81-3a58-4ea9-849c-ebea7f6b6360",

  "Power BI Service","00000009-0000-0000-c000-000000000000",

  "SharedWithMe","ffcb16e8-f789-467c-8ce9-f826a080d987",

  "SharePoint Online Web Client Extensibility","08e18876-6177-487e-b8b5-cf950c1e598c",

  "Signup","b4bddae8-ab25-483e-8670-df09b9f1d0ea",

  "Skype for Business Online","00000004-0000-0ff1-ce00-000000000000",

  "Sway","905fcf26-4eb7-48a0-9ff0-8dcc7194b5ba",

  "Universal Store Native Client","268761a2-03f3-40df-8a8b-c3db24145b6b",

  "Vortex [wsfed enabled]","5572c4c0-d078-44ce-b81c-6cbf8d3ed39e",

  "Yammer","00000005-0000-0ff1-ce00-000000000000",

  "Yammer Web","c1c74fed-04c9-4704-80dc-9f79a2e515cb",

  "Yammer Web Embed","e1ef36fd-b883-4dbf-97f0-9ece4b576fc6",

  "Windows Sign In","38aa3b87-a06d-4817-b275-7a316988d93b",

  "PowerApps - apps.powerapps.com","3e62f81e-590b-425b-9531-cad6683656cf",

  "make.powerapps.com","a8f7a65c-f5ba-4859-b2d6-df772c264e9d",

  "Microsoft Azure Information Protection","c00e9d32-3c8d-4a7d-832b-029040e7db99",

  "Microsoft Edge Enterprise New Tab Page","d7b530a4-7680-4c23-a8bf-c52c121d2e87",

  "Microsoft Account Controls V2","7eadcef8-456d-4611-9480-4fff72b8b9e2",

  "SharePoint Online Client Extensibility Web Application Principal","f7f708bc-b136-4073-b000-e730786c986e",

  "Power BI Desktop","7f67af8a-fedc-4b08-8b4e-37c4d127b6cf",

  "Office Online Print SSO","3ce44149-e365-40e4-9bb4-8c0ecb710fe6",

  "PowerApps","4e291c71-d680-4d0e-9640-0a3358e31177",

  "My Profile","8c59ead7-d703-4a27-9e55-c96a0054c8d2",

  "Apple Internet Accounts","f8d98a96-0999-43f5-8af3-69971c7bb423",

  "My Signins","19db86c3-b2b9-44cc-b339-36da233a3be2",

  "My Apps","2793995e-0a7d-40d7-bd35-6968ba142197"

];

let MicrosoftAppIdList = MicrosoftApps | summarize MicrosoftAppIds = make_list(AppId);

let AllMembers = materialize(SigninLogs

| where TimeGenerated between (startofday(ago (7d)) .. now())

| where ResultType == 0

| where UserPrincipalName matches regex @"\w+@\w+\.\w+"

| where UserType == "Guest");

AllMembers

| project TimeGenerated, Location, AppDisplayName, UserPrincipalName, AppId

| where tostring(AppId) !in (MicrosoftAppIdList)

| where AppDisplayName !startswith("Microsoft")

| where AppDisplayName !startswith("Office")

| where AppDisplayName !startswith("Windows")

| summarize UserCount = dcount(UserPrincipalName) by AppDisplayName

| top 5 by UserCount

| sort by UserCount desc

Conclusion

These queries demonstrate the power of KQL to visualize your Azure Active Directory. In combination with SquaredUp dashboards, you can powerfully visualize all your Azure AD utilization.

Appendix

KQL is a very interesting query language. Best training resources in my opinion are:

Share this article to LinkedInShare this article on XShare this article to Facebook
Ruben Zimmermann

Infrastructure Architect, SIG

Continue learning

Blog

Monitoring Azure Stack HCI with SCOM 2022

Blog

Monitor your Network with Link Dependency Monitoring

Blog

Azure MP part 3: Using it with SquaredUp

Blog

Fill SCOM gaps with Azure Monitor

Blog

Coffee break: hybrid monitoring with MSFT's Azure MP

Blog

Using SCOM to detect missing Active Directory site IP subnet allocations