KQL Search

Query Details

HomeAI ToolsDevice Query

Identity App Access Membersvs Guests

Query

//Creates a list of your applications and summarizes successful signins by members vs guests separated to total and distinct signins

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| project TimeGenerated, UserType, ResultType, AppDisplayName, UserPrincipalName
| where ResultType == 0
| summarize
    ['Total Member Signins']=countif(UserType == "Member"),
    ['Distinct Member Signins']=dcountif(UserPrincipalName, UserType == "Member"),
    ['Total Guest Signins']=countif(UserType == "Guest"),
    ['Distinct Guest Signins']=dcountif(UserPrincipalName, UserType == "Guest")
    by AppDisplayName
| sort by AppDisplayName asc

Explanation

This query creates a list of applications and summarizes the number of successful sign-ins by members and guests. It separates the total sign-ins and distinct sign-ins for each category. The query requires the Azure Active Directory - Signin Logs data connector. It filters the data for the past 30 days and projects the relevant columns. It then filters for successful sign-ins and summarizes the counts and distinct counts for members and guests separately. Finally, it sorts the results by the application display name in ascending order.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,UserType,ResultType,AppDisplayName,UserPrincipalName,TotalMemberSignins,DistinctMemberSignins,TotalGuestSignins,DistinctGuestSignins

Operators

whereagoprojectsummarizecountifdcountifbysort

Actions