Tag: monitoring

Understand your #AzureAD Sign-Ins with #KQL

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

AzureAD-LogAnalytics-Table
AzureAD-LogAnalytics-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.

Unique_SignIns_Total
Unique_SignIns_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_Over_Time_1of2
Unique_SignIns_Over_Time_1of2
Unique_SignIns_Over_Time_2of2
Unique_SignIns_Over_Time_2of2

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

Unique_SignIns_Over_Time_1of2
Unique_SignIns_Over_Time_1of2
Unique_SignIns_Over_Time_1of2
Unique_SignIns_Over_Time_2of2

Operating Systems

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

OperatingSystems
OperatingSystems
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.

OperatingSystems_1of2
OperatingSystems_1of2
OperatingSystems_2of2
OperatingSystems_2of2

Password Issues

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

Password_Issues
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 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.

Risky_SignIns
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

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

Risky_SignIns_1of2
Risky_SignIns_1of2
Risky_SignIns_2of2
Risky_SignIns_2of2

MFA Successful Sign Ins

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

MFA-SuccessfulSignIns
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

Top-5-Non-MS-Applications
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 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
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

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

Guets_users_Total
Guets_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)
Guets_users_Total_1of2
Guets_users_Total_1of2
Guets_users_Total_2of2
Guets_users_Total_2of2

Guest Domains Total

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

Guest_Domains_Total
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

Shows which Domains of Guest users show up most.

Top-5-Guest-User-Domains
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

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

Top-5-Apps-By-Guests
Top-5-Apps-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

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/

Monitor O365 with SCOM 2 (2 of 4)

As always with SCOM, the right Management Pack is required 😉

Active 365 MP by NiCE can track and monitor various aspects of the M365 suite.

The Management Pack

Core is a small executable runs on an OpsMgr Management Server or a OpsMgr – Gateway and performs the tests and synthetic transactions.
Only a few configuration steps are required 🙂

Active 365 MP Architecture

Two different monitoring modes are available.
If all Mailboxes have been already migrated, Online only Mode is the correct option. – Both support proxies. The architecture looks as below:

If Mailbox migration is still ongoing – or if some Exchange resources need to be kept on-premises, the hybrid mode fits.

Discovery

To make SCOM aware of your M365 tenant, some preparation steps are required.

User accounts with permissions on a SharePoint site, OneDrive and a Mailbox are needed. Additional permissions are configured within Azure Active Directory / Enterprise Applications.

Information about the user accounts need to be stored in configuration files.

Monitors

After details about the M365 tenant have been discovered, corresponding objects are created and appear in the diagram vies. Monitors can be enabled to perform many different tests to ensure and measure service availability.

M365 / Exchange (ExO pure or Hybrid)
– EWS Response Availability / Time (msec)
– Autodiscover Retrieval Availability
– Mailbox Logon Availability / Duration
– Free / Busy Check
– Mailbox Send & Receive Availability
– Mailbox Receive Latency
– Autodiscover Retrieval Duration
– Service Health Status  		M365 / SharePoint Online
– SpO Logon Latency
– SpO File Up- and Download Check
– SpO Log On Latency
– SpO Health Score
– Request Duration
– IIS Request Latency
– SpO Site Availability
– SpO Storage Usage (GB) Summary
– SpO Service Health Status  
M365 / OneDrive
– OneDrive Log On Latency
– OneDrive File Up- and Download Check
– OneDrive Availability
– OneDrive Service Health Status    		M365 / Teams
– Monitor Teams Chat
– Test LightTeam Chat Availability
– Team Service Health Status  

M365 Monitors – All Services Health Rollups

M365 Monitors – All Services Health Rollups
All M365 Services health rollups of all configured monitors with proxies to simulate different (user-) locations.

M365 – Exchange – Health State and Rollup

Monitors allow customization of thresholds as the default do not suit every environment. If needed, alerting can be enabled to notify about a decreasing performance or loss of service.

Performance Rules

Most transactions that are realized as a Monitor also store the retrieved value and allow reporting via graph plotting and understand trends.

E.g. Rules track Receive Latency, File Up- and Download Time and Request Duration

On Tenant Level:
– Active M365 License Units
– Consumed M365 License Units
– Warning M365 License Units
– Active Users
– Available M365 Licenses
– Identity Service Health, MDM Service Health, M365 Portal Health, M365 Client Application Health, Subscription Health, Number of ExO Mailboxes and others

On Services Level:
M365 – Teams Chat – Performance from 3 Proxy locations

This rule performs simplified test of Teams Chat features using the Graph API

M365 – Exchange Online – Number of Failed Free Busy checks

This rule performs simplified test of Teams Chat features using the Graph APIThis performance rule measures the quality of service (QOS) of the Exchange Online tenant by probing different Exchange Online Mailboxes for availability using resource sharing.

M365 – SharePoint Online – Upload speed from 3 Proxy locations

This performance rule tests upload file to SharePointSiteCollection via Web API

Proxy Locations

To simulate the speed and connectivity from different points in the organization, proxy locations can be configured. As the name states, it is based on web-proxy services that need to be stored in a configuration file.

Based on this information then, tests and probes are sent via the web-proxies, too.

Good to know

Note: By the time of writing, the Active 365 MP is version 3.1. At October 2020 NiCE released already version 3.2!

Download: https://www.nice.de/active-365-mp/

Monitor O365 with SCOM 1 (1 of 4)

Microsoft 365 is a managed service which offers Exchange, SharePoint, Teams, OneDrive and many more services worldwide. With it, responsibilities of IT staff evolved and changed.

Monitoring however is still a crucial aspect. In this short article, I will explain why SCOM a perfect solution for this is.

Monitoring – What and Why?

With M365, IT staff do not need to take care anymore of VMs, Windows patching, configuring the global mail-routing or managing the SharePoint farm. Microsoft is doing it all, so why bother with monitoring?

Although all runs in the cloud, these are still usual applications. They have bugs, they fail from time to time, respond slowly and perhaps behave weird. – All like before 😉

You might have found you already in the situation that a user called and complained about poor SharePoint performance. Another user called and mentioned that the whole Australian team cannot make Teams calls. Perhaps the secretary of the CEO called nervously mentioning she can’t book appointments because Free & Busy is not working well.

Microsoft plays an open deck here and shares news about outages with world. – There is a twitter account which tells things like:

Microsoft 365 Status @MSFT365Status | 2020-07-14
We’re investigating an issue affecting access to SharePoint Online and OneDrive for Business that is primarily impacting customers in EMEA. Additional details can be found in the admin center under SP218450 and OD218456.

In the M365 Admin portal we have a section about “Service Health”. In it we can find messages like:

A very limited number of users may intermittently be unable to access Exchange Online via any connection method

… but is that affecting us in this moment?

So, we can only look sad and tell our users’ that we’re opening a ticket? Luckily, we can do more 🙂

Requirements

Your environment needs to be at least running SCOM 2012 R2. Either a Management Server or a SCOM- Gateway needs to be able to reach Microsoft via Internet. – Proxies are supported, too.

The hard monitoring work is done by NiCE Active 365 MP. The beautiful visualizations are leverage Squared Up for SCOM. – Last piece is the free Data On Demand Management Pack ( always up to date via MP Catalog ) which is used to retrieve M365 meta information to provide more context.

Good to know

Using Squared Up’s Open Access dashboards can be consumed by every user.
No licenses are required. 🙂