Kusto - nested grouping

Question

I am trying to retrive, count and group data från Azure AD Sign in logs in Log analytics.

The indata contains a lot of properties, but those I am interested in is ClientAppUsed and AppDisplayname The indata looks like this:

Object 1

⮡ ClientAppUsed : Browser

⮡ AppDisplayName: Azure AD

Object 2

⮡ ClientAppUsed : Browser

⮡ AppDisplayName: Office Client App

Object 3

⮡ ClientAppUsed : POP

⮡ AppDisplayName: Microsoft Exchange Online

I want to group the ClientAppUsed types together and count each occurance of AppDisplayName under these. Like this:

Group Browser

⮡ AppDisplayName: Azure AD

⮡ Count of Azure AD

⮡ AppDisplayName: Office Client app

⮡ Count of Office Client App

Group POP

⮡ AppDisplayName: Microsoft Exchange Online

⮡ Count of Microsoft Exchange Online

What I have managed to do is to count it with the below query, but I want them grouped together instead of showing each occurance of AppDisplayName as a single row:

SignInLogs | summerize count() by ClientAppUsed, AppDisplayName

Any help is appreciated!

Answer 1

it wasn't entirely clear what is the output schema you're interested in, but here are a few alternatives you can try (or, update your question with a clearer description of the expected output schema & content)

1)

datatable(ClientAppUsed:string, AppDisplayName:string)
[
    'Browser', 'Azure AD',
    'Browser', 'Office Client App',
    'POP', 'Microsoft Exchange Online',
]
| summarize count() by ClientAppUsed, AppDisplayName
| summarize make_bag(pack(AppDisplayName, count_)) by ClientAppUsed

2)

datatable(ClientAppUsed:string, AppDisplayName:string)
[
    'Browser', 'Azure AD',
    'Browser', 'Office Client App',
    'POP', 'Microsoft Exchange Online',
]
| summarize count() by ClientAppUsed, AppDisplayName
| summarize make_list(pack("AppDisplayName", AppDisplayName, "Count", count_)) by ClientAppUsed