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 mos...
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
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
  • MichalZiemba's avatar
    MichalZiemba
    MCT
    Sep 17, 2020

    hspinto 

    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)

     

Resources