KQL Search

Query Details

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.