How to retreive custom property corresponding to another property in azure

Question

I am trying to write a kusto query to retrieve a custom property as below.

I want to retrieve count of pkgName and corresponding organization. I could retrieve the count of pkgName and the code is attached below.

let mainTable = union customEvents
    | extend name =replace("\n", "", name)
    | where iif('*' in ("*"), 1 == 1, name in ("*"))
    | where true;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = customDimensions["pkgName"]
    | extend dimension = iif(isempty(dimension), "<undefined>", dimension)
    | summarize hll = hll(itemId) by tostring(dimension)
    | extend Events = dcount_hll(hll)
    | order by Events desc
    | serialize rank = row_number()
    | extend dimension = iff(rank > 10, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension)
    | project ['pkgName'] = dimension, Counts = dcount_hll(merged);
cohortedTable

Please help me to get the organization along with each pkgName projected.

A simple query looks like that: `customEvents | summarize count(tostring(customDimensions.pkgName)) by aa=tostring(customDimensions.organization)`. You can try to modify it to meet your need. — Ivan Glasenberg, Jan 14 '21 at 06:45
The query given by @IvanYang is giving me the correct count. However I need to project corresponding organization name as well. Something like `packageName/organization` and its count — Parkavi, Jan 15 '21 at 04:33
do you mean project the `count of packageName`, and `packageName`, and `organization`? — Ivan Glasenberg, Jan 15 '21 at 04:39
yes exactly. I could only project the count and either the package name or organization, not the both. But I want to project all three together. — Parkavi, Jan 15 '21 at 05:01
I think we can create another table which contains `packageName` and its related `organization`, then join this table with yours. Let me give it a try, and update this thread later. — Ivan Glasenberg, Jan 15 '21 at 05:03
Really grateful for your time and response @IvanYang. Please update as an answer so that I can mark it. — Parkavi, Jan 15 '21 at 05:07

Ivan Glasenberg · Accepted Answer · 2021-01-15T06:25:23.340

Please try this simple query:

customEvents
| summarize counts=count(tostring(customDimensions.pkgName)) by pkgName=tostring(customDimensions.pkgName),organization=tostring(customDimensions.organization)

Please feel free to modify it to meet your requirement.

If the above does not meet your requirement, please try to create another table which contains pkgName and organization relationship. Then use join operator to join these tables. For example:

    //create a table which contains the relationship
    let temptable = customEvents
    | summarize by pkgName=tostring(customDimensions.pkgName),organization=tostring(customDimensions.organization);
    
    //then use the join operator to join these tables on the keyword pkgName.

How to retreive custom property corresponding to another property in azure

1 Answers1