Timechart with multiple dimension from custom properties

%3CLINGO-SUB%20id%3D%22lingo-sub-2126760%22%20slang%3D%22en-US%22%3ETimechart%20with%20multiple%20dimension%20from%20custom%20properties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2126760%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20followed%20%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%2F%23blade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2Fq%2FH4sIAAAAAAAAA1WOuwqDQBREe79iEAIKW2jAIoVpUqQLFpJ%252b1YkK7m64u%252bZFPj4PQsB6zjlMRTlFT1wHClGPhntaig7ssIXuXZJ3Kf7Azs02UA7aEGWJeIVKXEvvnXzl%252bIP62Rgt44PQlz75KUc9zUwVzpSWNowT%252fWJSKDKFTZECzR3NaJPFGYV8SN9t3gJth3oQ%252bsFNHUqss%252bgF6bL7icUAAAA%253d%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ethis%2C%3C%2FA%3E%26nbsp%3Bto%20achieve%20the%20subject.%20However%2C%20I%20cannot%20get%20the%20expected%20output%20because%20of%20the%20timestamp.%20I%20need%20to%20configure%20the%20time%20range%20in%20the%20dashboard%20and%20not%20set%20in%20the%20query%20so%20that%20I%20can%20get%20various%20charts%20depending%20on%20the%20time%20range%20I%20select.%20I%20anyway%20could%20do%20the%20same%20with%20the%20below%20Kusto%20code%20to%20arrive%20with%20a%20chart.%20However%2C%20my%20expectation%20is%20to%20achieve%20the%20same%20with%20a%20time%20chart(Image%20is%20also%20attached%20at%20the%20bottom).%20Your%20ideas%20and%20thoughts%20on%20this%20will%20be%20a%20huge%20help%20for%20me.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Elet%20filterByName%20%3D%20'event-name'%3B%0Alet%20events%20%3D%20dynamic(%5B%22*%22%5D)%3B%0Alet%20mainTable%20%3D%20union%20customEvents%0A%20%20%20%20%7C%20extend%20name%20%3Dreplace(%22%5Cn%22%2C%20%22%22%2C%20name)%0A%20%20%20%20%7C%20where%20'*'%20in%20(events)%20or%20name%20in%20(events)%0A%20%20%20%20%7C%20where%20iff(isempty(filterByName)%2C%201%20%3D%3D%201%2C%20name%20contains%20filterByName)%0A%20%20%20%20%7C%20where%20true%3B%0Alet%20queryTable%20%3D%20mainTable%3B%0Alet%20cohortedTable%20%3D%20queryTable%0A%20%20%20%20%7C%20extend%20dimension%20%3D%20customDimensions%5B%22vers%22%5D%0A%20%20%20%20%7C%20extend%20dimension%20%3D%20iif(isempty(dimension)%2C%20%22%3CUNDEFINED%3E%22%2C%20dimension)%0A%20%20%20%20%7C%20summarize%20hll%20%3D%20hll(itemId)%20by%20tostring(dimension)%0A%20%20%20%20%7C%20extend%20Events%20%3D%20dcount_hll(hll)%0A%20%20%20%20%7C%20order%20by%20Events%20desc%0A%20%20%20%20%7C%20serialize%20rank%20%3D%20row_number()%0A%20%20%20%20%7C%20extend%20dimension%20%3D%20iff(rank%20%26gt%3B%205%2C%20'Other'%2C%20dimension)%0A%20%20%20%20%7C%20summarize%20merged%20%3D%20hll_merge(hll)%20by%20tostring(dimension)%0A%20%20%20%20%7C%20project%20%5B'vers'%5D%20%3D%20dimension%2C%20Counts%20%3D%20dcount_hll(merged)%3B%0AcohortedTable%3C%2FUNDEFINED%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-02-11%20at%2007.43.58.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254408i5B78B33B2D3733DA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-02-11%20at%2007.43.58.png%22%20alt%3D%22Screenshot%202021-02-11%20at%2007.43.58.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2126760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMonitoring%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2154628%22%20slang%3D%22en-US%22%3ERe%3A%20Timechart%20with%20multiple%20dimension%20from%20custom%20properties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2154628%22%20slang%3D%22en-US%22%3EThis%20could%20be%20easily%20done%20by%20querying%20the%20required%20time%20chart%20in%20a%20new%20table%20defined%20as%20below%3A%3CBR%20%2F%3E%60%60%60let%20filterByName%20%3D%20'event-name'%3B%3CBR%20%2F%3Elet%20events%20%3D%20dynamic(%5B%22*%22%5D)%3B%3CBR%20%2F%3Elet%20mainTable%20%3D%20union%20customEvents%3CBR%20%2F%3E%7C%20extend%20name%20%3Dreplace(%22%5Cn%22%2C%20%22%22%2C%20name)%3CBR%20%2F%3E%7C%20where%20'*'%20in%20(events)%20or%20name%20in%20(events)%3CBR%20%2F%3E%7C%20where%20iff(isempty(filterByName)%2C%201%20%3D%3D%201%2C%20name%20contains%20filterByName)%3CBR%20%2F%3E%7C%20where%20true%3B%3CBR%20%2F%3Elet%20queryTable%20%3D%20mainTable%3B%3CBR%20%2F%3Elet%20cohortedTable%20%3D%20queryTable%3CBR%20%2F%3E%7C%20extend%20dimension%20%3D%20customDimensions%5B%22vers%22%5D%3CBR%20%2F%3E%7C%20extend%20dimension%20%3D%20iif(isempty(dimension)%2C%20%22%3CUNDEFINED%3E%22%2C%20dimension)%3CBR%20%2F%3E%7C%20summarize%20hll%20%3D%20hll(itemId)%20by%20tostring(dimension)%3CBR%20%2F%3E%7C%20extend%20Events%20%3D%20dcount_hll(hll)%3CBR%20%2F%3E%7C%20order%20by%20Events%20desc%3CBR%20%2F%3E%7C%20serialize%20rank%20%3D%20row_number()%3CBR%20%2F%3E%7C%20extend%20dimension%20%3D%20iff(rank%20%26gt%3B%205%2C%20'Other'%2C%20dimension)%3CBR%20%2F%3E%7C%20summarize%20merged%20%3D%20hll_merge(hll)%20by%20tostring(dimension)%3B%3CBR%20%2F%3EcohortedTable%3CBR%20%2F%3E%7C%20union%20customEvents%3CBR%20%2F%3E%7C%20extend%20dimension%20%3D%20customDimensions%5B%22vers%22%5D%3CBR%20%2F%3E%7C%20summarize%20Count%3Dcount()%20by%20tostring(dimension)%2C%20bin(timestamp%2C%201h)%3CBR%20%2F%3E%7C%20render%20timechart%20%60%60%60%3C%2FUNDEFINED%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have followed this, to achieve the subject. However, I cannot get the expected output because of the timestamp. I need to configure the time range in the dashboard and not set in the query so that I can get various charts depending on the time range I select. I anyway could do the same with the below Kusto code to arrive with a chart. However, my expectation is to achieve the same with a time chart(Image is also attached at the bottom). Your ideas and thoughts on this will be a huge help for me.

 

let filterByName = 'event-name';
let events = dynamic(["*"]);
let mainTable = union customEvents
    | extend name =replace("\n", "", name)
    | where '*' in (events) or name in (events)
    | where iff(isempty(filterByName), 1 == 1, name contains filterByName)
    | where true;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = customDimensions["vers"]
    | 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 > 5, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension)
    | project ['vers'] = dimension, Counts = dcount_hll(merged);
cohortedTable

 

Screenshot 2021-02-11 at 07.43.58.png

 

 

1 Reply

 

let filterByName = 'distribution-download';
let events = dynamic(["*"]);
let mainTable = union customEvents
    | extend name =replace("\n", "", name)
    | where '*' in (events) or name in (events)
    | where iff(isempty(filterByName), 1 == 1, name contains filterByName)
    | where true;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = customDimensions["downloadedDistVersion"]
    | 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 > 5, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension);
cohortedTable
| union customEvents
| extend dimension = customDimensions["downloadedDistVersion"] 
| summarize Count=count() by tostring(dimension), bin(timestamp, 1h)
| render timechart 

 

This could be easily done by querying the required time chart in a new table defined as above: