Azure application gateway accesslogs query - Timetaken

%3CLINGO-SUB%20id%3D%22lingo-sub-1057121%22%20slang%3D%22en-US%22%3EAzure%20application%20gateway%20accesslogs%20query%20-%20Timetaken%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1057121%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20all%20my%20Application%20Gateway%20logs%20going%20to%20a%20Log%20Analytics%20workspace.%20I%20want%20to%20query%20this%20data%20to%20show%20any%20URIs%20with%20%3CSTRONG%3Elatency%20and%20period%20of%20the%20site%20responses%20from%20backend%20pools%3C%2FSTRONG%3E.%20Can%20someone%20point%20me%20in%20the%20right%20direction%20of%20how%20to%20query%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3EDarwin%20Vinoth%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1057121%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAutomation%20%26amp%3B%20Control%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EHands-on-Labs%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMonitoring%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1061241%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20application%20gateway%20accesslogs%20query%20-%20Timetaken%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1061241%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20Darwin.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%20of%20a%20query%20that%20gives%20you%20some%20statistics%20for%20the%20last%203%20days%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Category%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'ApplicationGatewayAccessLog'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20RequestCount%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%2C%20AvgTimeTaken%20%3D%20avg(timeTaken_d)%2C%20percentiles(timeTaken_d%2C%20%3C%2FSPAN%3E%3CSPAN%3E50%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E75%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E95%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E99%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20backendPoolName_s%2C%20requestUri_s%2C%20httpMethod_s%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20AvgTimeTaken%20desc%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EIf%20you%20want%20to%20know%20which%20columns%20the%20AppGW%20access%20logs%20contain%20and%20then%20filter%2Fsummarize%20by%20other%20columns%2C%20run%20this%20query%20(it%20retuns%20a%2010%20logs%20sample)%3A%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Category%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'ApplicationGatewayAccessLog'%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Etake%3C%2FSPAN%3E%20%3CSPAN%3E10%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EIf%20you%20want%20to%20know%20more%20about%20the%20Kusto%20query%20language%2C%20you%20can%20start%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2F%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EIf%20you%20can't%20figure%20out%20how%20to%20write%20a%20query%20to%20answer%20a%20specific%20question%20you%20might%20have%2C%20please%20let%20me%20know.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1062311%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20application%20gateway%20accesslogs%20query%20-%20Timetaken%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1062311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20such%20a%20query.%20I%20would%20like%20to%20set%20my%20own%20set%20of%20queries.%20Is%20there%20any%20blog%20I%20can%20refer%20to%20get%20know%20about%20such%20query%20language.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDarwin%20Vinoth%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1063714%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20application%20gateway%20accesslogs%20query%20-%20Timetaken%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F263930%22%20target%3D%22_blank%22%3E%40Vinoth_Azure%3C%2FA%3E%2C%26nbsp%3Bthere%20isn't%20a%20blog%20dedicated%20to%20the%20Kusto%20Query%20Language%20that%20I%20am%20aware%20of.%20However%2C%20you%20can%20also%20refer%20to%20this%20good%20article%20on%20how%20to%20get%20started%20with%20Log%20Analytics%20queries%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Fget-started-queries%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Fget-started-queries%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have all my Application Gateway logs going to a Log Analytics workspace. I want to query this data to show any URIs with latency and period of the site responses from backend pools. Can someone point me in the right direction of how to query this?

 

Thanks 

Darwin Vinoth

 

3 Replies
Highlighted

Hello, Darwin.

 

Here is an example of a query that gives you some statistics for the last 3 days:

 

AzureDiagnostics
| where Category == 'ApplicationGatewayAccessLog' and TimeGenerated > ago(3d)
| summarize RequestCount = count(), AvgTimeTaken = avg(timeTaken_d), percentiles(timeTaken_d, 50, 75, 95, 99) by backendPoolName_s, requestUri_s, httpMethod_s
| order by AvgTimeTaken desc
 
If you want to know which columns the AppGW access logs contain and then filter/summarize by other columns, run this query (it retuns a 10 logs sample):
 
AzureDiagnostics
| where Category == 'ApplicationGatewayAccessLog'
| take 10
 
If you want to know more about the Kusto query language, you can start here.
 
If you can't figure out how to write a query to answer a specific question you might have, please let me know.
 
Highlighted

@hspinto 

 

Thanks for such a query. I would like to set my own set of queries. Is there any blog I can refer to get know about such query language.

 

Thanks

Darwin Vinoth  

Highlighted

@Vinoth_Azure, there isn't a blog dedicated to the Kusto Query Language that I am aware of. However, you can also refer to this good article on how to get started with Log Analytics queries:

 

https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/get-started-queries