KQL query for retrieving records

%3CLINGO-SUB%20id%3D%22lingo-sub-1154679%22%20slang%3D%22en-US%22%3EKQL%20query%20for%20retrieving%20records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1154679%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20new%20to%20KQL%20and%20have%20been%20stuck%20at%20a%20certain%20query%20for%20quite%20sometime.%20Lets%20say%20we%20have%20table%20with%20various%20users%20and%20they%20execute%20some%20commands%20which%20has%20been%20recorded%20in%20log%20with%20the%20username%20and%20the%20time%20taken%20by%20the%20user%20to%20execute%20that%20command%20%2C%20so%20what%20i%20want%20to%20do%20is%20to%20retrieve%20the%20top%2015%20entries%20for%20each%20unique%20user%20according%20to%20the%20duration%20of%20time%20they%20have%20taken%20to%20execute%20the%20command%20which%20is%20greater%20than%202%20minutes.%20Any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1154679%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1155532%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20retrieving%20records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1155532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F546963%22%20target%3D%22_blank%22%3E%40Fraz_Khan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20didn't%20mention%20the%20table%2C%20or%20show%20an%20example%20row%20of%20data%2C%20here%20is%20a%20generic%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EHeartbeat%0A%7C%20summarize%20count()%20by%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWhich%20gets%20me%2C%20two%20columns%20(Like%20your%20%3CSTRONG%3EUser%3C%2FSTRONG%3E%20and%26nbsp%3B%3CSTRONG%3ECount%3C%2FSTRONG%3E%20columns%20you%20mentioned)%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_2%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA%25252FNITSwqSUpNLOHlqlEoLs3NTSzKrEpVSM4vzSvR0FRIqlRwzs8tKC1JLQIpKM9ILYJKxivYKRgaGBiAhEvyCxRMQWqhMimpxckKvFwAB3tf4FwAAAA%25253D%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%26nbsp%3B(Demo%20data)%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_3%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EHeartbeat%0A%7C%20summarize%20count()%20by%20Computer%0A%7C%20where%20count_%20%26gt%3B%201000%0A%7C%20top%2015%20by%20count_%20desc%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorClive%20Watson_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EWe'd%20then%20add%20a%20line%20to%20only%20show%20the%20values%20above%201000%20(2mins%20in%20your%20example)%20and%20then%20the%20Top%2015%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExample%20results%20(top%205)%20to%20keep%20it%20short%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EComputer%3C%2FTH%3E%0A%3CTH%3Ecount_%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Ehardening-demo%3C%2FTD%3E%0A%3CTD%3E2877%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EContosoJbFwJb%3C%2FTD%3E%0A%3CTD%3E1441%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Erancher-node-2%3C%2FTD%3E%0A%3CTD%3E1441%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Egangams-kind-k8s-cluster-master%3C%2FTD%3E%0A%3CTD%3E1441%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EContosoAppSrv1%3C%2FTD%3E%0A%3CTD%3E1441%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20know%20how%20the%20column%20that%20has%20%222%20mins%22%20is%20expressed%2C%20so%20you%20may%20need%20to%20turn%20it%20into%20a%20string%20or%20an%20integer%20first%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I am new to KQL and have been stuck at a certain query for quite sometime. Lets say we have table with various users and they execute some commands which has been recorded in log with the username and the time taken by the user to execute that command , so what i want to do is to retrieve the top 15 entries for each unique user according to the duration of time they have taken to execute the command which is greater than 2 minutes. Any help would be appreciated.

 

Thank you.

1 Reply
Highlighted

@Fraz_Khan 

 

As you didn't mention the table, or show an example row of data, here is a generic example:

 

Heartbeat
| summarize count() by Computer

Which gets me, two columns (Like your User and Count columns you mentioned)

 
 

Go to Log Analytics and run query (Demo data)

 
Heartbeat
| summarize count() by Computer
| where count_ > 1000
| top 15 by count_ desc 

 

 

We'd then add a line to only show the values above 1000 (2mins in your example) and then the Top 15

 

Example results (top 5) to keep it short

 

Computer count_
hardening-demo 2877
ContosoJbFwJb 1441
rancher-node-2 1441
gangams-kind-k8s-cluster-master 1441
ContosoAppSrv1 1441

 

I don't know how the column that has "2 mins" is expressed, so you may need to turn it into a string or an integer first?