SOLVED

Help requested with log analytics query for Application Gateway timechart

%3CLINGO-SUB%20id%3D%22lingo-sub-198330%22%20slang%3D%22en-US%22%3EHelp%20requested%20with%20log%20analytics%20query%20for%20Application%20Gateway%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198330%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3ECan%20anyone%20help%20out%20a%20starting%20log%20analytics%20rookie%3F%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20troubleshoot%20performance%20on%20an%20Application%20Gateway%20and%20noticed%20a%20specific%20IP%20has%20a%20high%20amount%20of%20hits%20compared%20to%20the%20others%20(factor%2010000).%3C%2FP%3E%3CP%3EI%20want%20to%20filter%20out%20all%20requests%20for%20that%20specific%20IP%20address%20and%20set%20on%20a%20timeline%20how%20many%26nbsp%3Brequests%20have%20been%20send%20by%20this%20IP%26nbsp%3Bin%20time%26nbsp%3Bso%20I%20cancorrelate%20traffic%20originating%20from%20that%20IP%20with%20the%20performance%20issues%20we%20experienced.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20already%20have%20is%20quite%20limited%3A%3C%2FP%3E%3CP%3Esearch%20in%20(AzureDiagnostics)%20ResourceType%20%3D%3D%20%22APPLICATIONGATEWAYS%22%20and%20Resource%20%3D%3D%20%22mygateway%22%3CBR%20%2F%3E%7C%20where%20clientIP_s%20%3D%3D%20%221.1.1.1%22%3CBR%20%2F%3E%7C%20render%20timechart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20feedback!%3C%2FP%3E%3CP%3EBart%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-198330%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-199693%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20requested%20with%20log%20analytics%20query%20for%20Application%20Gateway%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199693%22%20slang%3D%22en-US%22%3E%3CP%3EWonderful!%3C%2FP%3E%3CP%3EThis%20indeed%20returns%20the%20information%20that%20I'm%20looking%20for%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EBart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-199338%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20requested%20with%20log%20analytics%20query%20for%20Application%20Gateway%20timechart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199338%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Bart%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understand%20your%20question%20correctly%2C%20you're%20looking%20for%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLearn%2FTutorials%2FAggregation-functions%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ebin()%3C%2FA%3Ecommand.%20I've%20also%20cleaned%20up%20the%20rest%20of%20your%20query%20to%20be%20more%20performant.%20Try%20to%20avoid%20%22search%22%20whenever%20possible%20to%20improve%20your%20query%20times%3B%20in%20this%20case%2C%20since%20you%20know%20the%20table%20name%20and%20column%20names%20where%20your%20data%20is%2C%20filter%20by%20them%20directly.%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20ResourceType%20%3D%3D%20%22APPLICATIONGATEWAYS%22%20and%20Resource%20%3D%3D%20%22mygateway%22%0A%7C%20where%20clientIP_s%20%3D%3D%20%221.1.1.1%22%0A%7C%20summarize%20count()%20by%20bin(TimeGenerated%2C%201h)%0A%7C%20render%20timechart%3C%2FPRE%3E%0A%3CP%3ENote%20that%20while%20I'm%20using%20%221h%22%20here%20as%20the%20size%20of%20bucket%26nbsp%3Bwhich%20you%20want%20to%20examine%2C%20I%20could%20just%20as%20easily%20say%20%222m%22%20(2%20minute%20buckets)%2C%20%2230s%22%20(30%20seconds)%2C%20etc.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3E-Evgeny%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

Can anyone help out a starting log analytics rookie?

I am trying to troubleshoot performance on an Application Gateway and noticed a specific IP has a high amount of hits compared to the others (factor 10000).

I want to filter out all requests for that specific IP address and set on a timeline how many requests have been send by this IP in time so I cancorrelate traffic originating from that IP with the performance issues we experienced.

 

What I already have is quite limited:

search in (AzureDiagnostics) ResourceType == "APPLICATIONGATEWAYS" and Resource == "mygateway"
| where clientIP_s == "1.1.1.1"
| render timechart

 

Thanks for your feedback!

Bart

2 Replies
Highlighted
Solution

Hi Bart,

 

If I understand your question correctly, you're looking for the bin() command. I've also cleaned up the rest of your query to be more performant. Try to avoid "search" whenever possible to improve your query times; in this case, since you know the table name and column names where your data is, filter by them directly.

AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and Resource == "mygateway"
| where clientIP_s == "1.1.1.1"
| summarize count() by bin(TimeGenerated, 1h)
| render timechart

Note that while I'm using "1h" here as the size of bucket which you want to examine, I could just as easily say "2m" (2 minute buckets), "30s" (30 seconds), etc. 

 

Thanks,

-Evgeny

 

 

Highlighted

Wonderful!

This indeed returns the information that I'm looking for

 

Thanks,

Regards,

Bart