Sep 16 2020
- last edited on
Apr 08 2022
I have built a simple query to show the result of a specific conditional access policy, but I am struggling with device info. Cannot aggregate the info into one row.
First, let's start with the most simple query and filter on the specific sign-in event (CorrelationId).
SigninLogs | where TimeGenerated > ago(1d) | mvexpand PolicyResults = ConditionalAccessPolicies | mvexpand Device = DeviceDetail | where (PolicyResults.id == "####-004016656ca4" or PolicyResults.id == "####-a2e429a5325d") and PolicyResults.result != "reportOnlyNotApplied" |where CorrelationId == "#####-10c620ed816d" |project Mail=UserPrincipalName, User_Name=UserDisplayName, Application_name=AppDisplayName, Protocol=ClientAppUsed, Device
It gives me such results:
|project Mail=UserPrincipalName, User_Name=UserDisplayName, Application_name=AppDisplayName, Protocol=ClientAppUsed, Device.displayName, Device.operatingSystem
Sep 16 2020 01:23 PM
In order to summarize you first need to figure out what are the expected results, and how you want to summarize your results, I couldn't figure out from your description what is the desired result set.
For summarize you should use an aggregation function (if you don't use one the functionality is the same as the 'distinct' operator).
I'd suggest here the simplest function which is 'count()', it will give you the count of unique combinations occurrences of the columns mentioned on the right hand of the expression (after 'by'), try adding the following line to the end of your query:
| summarize count() by Mail, User_Name, Application_name, Protocol, tostring(Device.displayName), tostring(Device.operatingSystem)
Here is a link to 'summarize' documentation: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/summarizeoperator
Sep 17 2020 01:27 PM
Sorry if I were not clear about my goal and thank you for your tip.
If I run a simple query:
SigninLogs |where CorrelationId == "###-#-#-10c620ed816d"
Sep 17 2020 02:30 PM
Thank you very much.
I have combined it to this (maybe not so elegant) query and it does what I wanted.
SigninLogs | where TimeGenerated > ago(14d) | mvexpand PolicyResults = ConditionalAccessPolicies | where PolicyResults.id == "e60d3233-####-004016656ca4" and PolicyResults.result != "reportOnlyNotApplied" | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser, displayName=tostring(DeviceDetail.displayName),deviceId= tostring(DeviceDetail.deviceId), trustType= tostring(DeviceDetail.trustType) |summarize count() by Mail=UserPrincipalName, User_Name=UserDisplayName, Application_name=AppDisplayName, Protocol=ClientAppUsed, displayName, deviceId, trustType , tostring(OS), tostring(Browser)
Sep 25 2020 06:21 AM
The legacy and obsolete form of the operator
mvexpand has a default row limit of 128.