Teaser

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

Introduction

Having Azure AD as identity provider offers convenient Single Sign On experience for users and 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-AzureActiveDirectory

Shortly after enabling of logging, events are logged in the SigninLogs table. – Nearly all queries in this blog are against this table.

Links about learning KQL can be found in the appendix. – Particular questions about code will be answered. – Also suggestions for better queries are appreciated! 😉

Configuration & Code

In the remaining, most of the visualizations will be explained in detail. Queries are written in #KQL and which finalizes this article.

Unique SignIns Total

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

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 Sign Ins over Time

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

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 choice here.

Operating Systems

Used operating systems are mostly correct identified and show clearly from where Azure AD applications are consumed.

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"
                            )

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

Password Issues

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

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 realized 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 occurred.

AADUserRiskEvents is the table which stores this information.

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

Azure – Log Analytics ( grid ) is used for the table. Conditional formatting help to spot most serious events.

MFA Successful Sign Ins

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

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

Usage trends of applications can be retrieved. Microsoft recently released a website which enumerates many of its applications. – Unfortunately, not all and as a static website.

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

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.

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

Enumerates the total number of individual Guest accounts who signed in.

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 Domains Total

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

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

Shows which Domains of Guest users show up most.

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

This identifies which applications are used most by guests. Most popular Microsoft Applications are excluded.

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. In combination with SquaredUp-Dashboards your Azure AD utilization can be well explained.

Appendix

KQL is a very interesting query language. Best training resources in my opinion are: https://www.pluralsight.com/courses/kusto-query-language-kql-from-scratch

https://www.pluralsight.com/courses/microsoft-azure-data-explorer-starting

#SquaredUp the tool which is used for Visualization can be found on: https://ds.squaredup.com/

Month: March 2023

Understand your #AzureAD Sign-Ins with #KQL