Dashboard Stories

Dashboard and analyze your Azure Entra ID (Azure Active Directory) SignIns with KQL

Learn how to analyze authentication patterns, detect anomalies, and enhance security monitoring using powerful KQL queries.

Sameer Mhaisekar

Developer Advocate, SquaredUp

Azure Entra ID (formerly Azure Active Directory) Sign-In logs provide a wealth of information about user authentication activity, including who signed in, from where, and with what device. However, to truly unlock the value of this data, it's essential to analyze it effectively using KQL. Let's explore how you can leverage SquaredUp's dashboard to run KQL to analyze your Azure Entra ID Sign-In logs, identify authentication trends, detect security anomalies, and optimize your overall identity management strategy.

Configuration in Azure

This Azure AD dashboard covers the following:

Unique sign-ins
Unique sign-ins over time
Risky sign-ins
Top applications
Guest users
Guest domains

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.

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

Now that we have the data in Azure, let's create a dashboard for it in SquaredUp.

SquaredUp dashboard walkthrough

To start, deploy the Azure plugin.

After that is done, browse through the multiple data streams (which enable you to retrieve all things Azure), and select the KQL data stream.

Next, we select the correct Azure Log Analytics workspace where we're streaming these logs.

In the parameters section, paste in your KQL query and that should return the data from your workspace. Choose the right visualization type based on what data you want to display on the dashboard, and you're done! Simple as that.

Here are the KQL queries I'm using for my dashboard.

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

Unique Sign-ins 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)

Risky Sign-ins

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

Top 5 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 applications by guests

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

Sign-ins from guest 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