KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Summarize Guest Domainby Type

Query

//Summarize guest activity by external Azure AD guests (those that belong to another Azure AD tenant) vs External Guests (such as Gmail) to your tenant
//For each domain list the total number of signins and distinct user 

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

SigninLogs
| where TimeGenerated > ago (30d)
| where UserType == "Guest"
| where ResultType == 0
| extend ['Guest Domain'] = tostring(split(UserPrincipalName, "@")[1])
| summarize
    ['External Azure AD Guest Logins']=countif(ResourceTenantId != HomeTenantId),
    ['External Azure AD Guest Distinct Users']=dcountif(UserPrincipalName, ResourceTenantId != HomeTenantId),
    ['External Guest Logins']=countif(ResourceTenantId == HomeTenantId),
    ['External Guest Distinct Users']=dcountif(UserPrincipalName, ResourceTenantId == HomeTenantId)
    by ['Guest Domain']

Explanation

This query summarizes guest activity in your Azure AD tenant. It distinguishes between external Azure AD guests (from other Azure AD tenants) and external guests (such as Gmail users). It lists the total number of sign-ins and distinct users for each domain. The query requires the Azure Active Directory - Signin Logs data connector.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,UserType,ResultType,GuestDomain,ResourceTenantId,HomeTenantId,UserPrincipalName

Operators

whereago==extendtostringsplitsummarizecountif!=dcountifby

Actions