question

KunalMehta-7099 avatar image
0 Votes"
KunalMehta-7099 asked tbgangav-MSFT commented

Ho to use KQL to get top 10 TimeGenerated Log entries for each customer

Consider i have 10 customers, and i need top 10 log entries by Time for each Customer

T
| top 10 by Timegenerated ????

azure-monitor
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

tbgangav-MSFT avatar image
0 Votes"
tbgangav-MSFT answered tbgangav-MSFT commented

Hi @KunalMehta-7099,

If you have a Column for Customer in your table (say T) then you can try one of the below 2 approaches (one using partition operator and another one using union operator).

Using partition operator:

 T
 | where Customer has_any ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG', 'HHH', 'III', 'JJJ')
 | partition by Customer
 (
     top 10 by TimeGenerated 
     | project TimeGenerated, Customer
 )

Using union operator:

 let View_1 = view () { T | where Customer == "AAA" | top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_2 = view () { T | where Customer == "BBB" |top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_3 = view () { T | where Customer == "CCC" | top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_4 = view () { T | where Customer == "DDD" |top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_5 = view () { T | where Customer == "EEE" | top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_6 = view () { T | where Customer == "FFF" |top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_7 = view () { T | where Customer == "GGG" | top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_8 = view () { T | where Customer == "HHH" |top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_9 = view () { T | where Customer == "III" | top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 let View_10 = view () { T | where Customer == "JJJ" |top 10 by TimeGenerated | project TimeGenerated, Customer }; 
 union withsource=TableName View_1, View_2, View_3, View_4, View_5, View_6, View_7, View_8, View_9, View_10 

where T is table name, Customer is column name of customers, AAA to JJJ are 10 sample customer name

However, if we think from looping standpoint then I believe there are other possible ways as well using operators like top-nested, mv-apply, mv-expand. If interested, take a look at examples section in each of these operator articles.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @tbgangav-MSFT ,

Thanks a lot for the quick help, i was able to resolve issue using "partition by"...

0 Votes 0 ·

Hi @KunalMehta-7099,

Glad to know that you were able to resolve the issue.

0 Votes 0 ·