Need assistance in getting the summary of user domains from sentinel signinglogs.
SigninLogs
| where AppId == "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx"
| extend UserDomains = split(UserPrincipalName,'@')[1]
| summarize TotalAttempts = count(), Failed=countif(ResultType !=0), Succeded=countif(ResultType ==0), LastAttempt = max(TimeGenerated), FirstAttempt = min(TimeGenerated), CountofUniqueID = dcount(UserPrincipalName), DomainCount = dcount(tostring(UserDomains))
The result has most of the information I need. However, I need little bit more clarity. The objective of this report is to understand how may external users are consuming this applications and from which all domains they are accessing. So I need to make a summary based on internal users and external users.
How can I get the the count of all signed in users who are having a domain name ending with *mydomain.com (covering the root domain and child domains)
How can I get the the count of user domains specific to external users, ie the unique user domains - internal user domains (anything ending with *mydomain.com) Is there a way to concatenate all the external domains with ";" as a delimiter ? In PowerShell, we use -join ";". Anything similar for KQL?
Appreciate your help on this.