Total Succesful Sign-Ins by Operating System
Sign Ins By OS
Query
EntraIdSignInEvents
| where isnotempty(UserAgent)
// Filter for successful sign ins only
| where ErrorCode == 0
| extend ParsedAgent = parse_json(parse_user_agent(UserAgent, "os"))
| extend OperatingSystem = strcat(tostring(ParsedAgent.OperatingSystem.Family), " ", tostring(ParsedAgent.OperatingSystem.MajorVersion))
| summarize Total = count() by OperatingSystem
| sort by TotalAbout this query
Total Succesful Sign-Ins by Operating System
Query Information
Description
This query can be used to detect rare operating systems that are used to sign into your tenant. For example your company only has Windows company devices and you have sign ins with MacOS, those can ben intersting to investigate.
This query can also be used to determine with Operting Systems need to be added to your Conditional Access Policies.
Defender XDR
Sentinel
SigninLogs
| where isnotempty(UserAgent)
// Filter for successful sign ins only
| where ResultType == 0
| extend ParsedAgent = parse_json(parse_user_agent(UserAgent, "os"))
| extend OperatingSystem = strcat(tostring(ParsedAgent.OperatingSystem.Family), " ", tostring(ParsedAgent.OperatingSystem.MajorVersion))
| summarize Total = count() by OperatingSystem
| sort by Total
Explanation
This query is designed to analyze successful sign-in events to identify the operating systems used during these sign-ins. Here's a simple breakdown of what the query does:
-
Data Source: The query pulls data from sign-in logs, specifically looking at events where a user agent string is present.
-
Filter for Success: It filters the data to include only successful sign-ins. This is done by checking if the error code (or result type) is zero, which indicates success.
-
Parse User Agent: The query extracts and parses the operating system information from the user agent string. It identifies the operating system family (like Windows, MacOS, etc.) and its major version.
-
Count and Summarize: It counts the total number of successful sign-ins for each operating system.
-
Sort Results: Finally, it sorts the results by the total number of sign-ins for each operating system.
The purpose of this query is twofold:
- To detect any unusual or rare operating systems being used to sign into the system, which might warrant further investigation.
- To help determine which operating systems should be included in conditional access policies, ensuring that only approved systems are allowed access.
Details
Bert-Jan Pals
Released: February 27, 2026
Tables
Keywords
Operators