Query Details
Identity Visualize Totalvs Distinctsignins
Query
//Visualize the difference been total and distinct user sign ins to an app per day
//Data connector required for this query - Azure Active Directory - Signin Logs
//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(90d)
| where AppDisplayName == "Office 365 Exchange Online"
| where ResultType == 0
| summarize ['Total Signins']=count(), ['Distinct user signins']=dcount(UserPrincipalName) by bin(TimeGenerated, 1d)
| render timechart
with (
title="Total vs Distinct signins to Exchange Online",
xtitle="Day",
ytitle="Count")
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting with Azure AD P2 License
AADSignInEventsBeta
| where Timestamp > ago(90d)
| where Application == "Office 365 Exchange Online"
| where ErrorCode == 0
| summarize ['Total Signins']=count(), ['Distinct user signins']=dcount(AccountUpn) by bin(Timestamp, 1d)
| render timechartExplanation
This query visualizes the difference between the total number of sign-ins and the number of distinct users signing in to an app per day. It uses different data connectors depending on the query type.
For the Microsoft Sentinel query, it uses the Azure Active Directory - Signin Logs data connector. It filters the sign-in logs for the Office 365 Exchange Online app, selects successful sign-ins (ResultType == 0), and groups the data by day. It then counts the total sign-ins and the distinct user sign-ins and renders a time chart.
For the Advanced Hunting query, it uses the Advanced Hunting with Azure AD P2 License data connector. It filters the sign-in events for the Office 365 Exchange Online app, selects successful sign-ins (ErrorCode == 0), and groups the data by day. It also counts the total sign-ins and the distinct user sign-ins and renders a time chart.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators