Forum Discussion

MichalZiemba's avatar
MichalZiemba
MCT
Sep 16, 2020
Solved

How to aggregate results from a query

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:

If I refer to specific properties in the Device and get them in separate columns, still got multiply raws as result. 

 

 

 

|project Mail=UserPrincipalName, User_Name=UserDisplayName, Application_name=AppDisplayName, Protocol=ClientAppUsed, Device.displayName, Device.operatingSystem

 

 

 

But still got results in multiply rows.
I guess "summarize" can help but don't know how to use it. 
 
Any tips?
 
azure monitor
Query Language
  • hspinto's avatar
    hspinto
    Sep 17, 2020

    MichalZiemba 

     

    instead of mv-expanding DeviceDetail (you're creating new rows) you should extend it to new columns, by using the extend operator, such as

     

    | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser

5 Replies

  • Rafi_Rabo's avatar
    Rafi_Rabo
    Icon for Microsoft rankMicrosoft
    Sep 16, 2020

    Hi MichalZiemba,

     

    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

     

    Thanks,

    Rafi 

    • MichalZiemba's avatar
      MichalZiemba
      MCT
      Sep 17, 2020

      Hi Rafi_Rabo 

      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"

       

      I got 36 rows as a result. They are all looking the same, referring to the same sign-in event, comes from the same device. They differ with some details about conditional access policy results. 
      So what I was trying to achieve is filter out the desired policy ID from ConditionalAccessPolicies and list the user, app, and device details. 
      And I wanted to present it that way, that if some of the rows are sign-ins from the same app and device, list it only once. 
      The result of this query would be presented to the ServiceDesk, where they instruct the user which device and which app triggers the conditional access before they block the user. 
       
      I hope it is more clear now. 
      • hspinto's avatar
        hspinto
        Icon for Microsoft rankMicrosoft
        Sep 17, 2020

        MichalZiemba 

         

        instead of mv-expanding DeviceDetail (you're creating new rows) you should extend it to new columns, by using the extend operator, such as

         

        | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser

Resources