Log Query – Request errors by host and path - How to exlude paths with containing words

Nibbler 616

Hi there,

I want to make a Query, on the Application Gateway and Front Door, shwoing 500 errors - but excluding 500 pages and certain pages/paths containing specific words.

Any good takes on how to do this?

Script that is being used on the AFD

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| where isReceivedFromClient_b == true
| where toint(httpStatusCode_s) >= 500
| extend ParsedUrl = parseurl(requestUri_s)
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), StatusCode = httpStatusCode_s
| order by RequestCount desc

Thanks for help.

1 answer

Ryan Hill 25,661 Reputation points Microsoft Employee

2021-02-08T03:13:47.813+00:00

Hi @KE1980,

I think what you may be looking for is | where RequestUri_s !in('<not>'.'<this>','<word'>). See https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/inoperator for more info.

Regards,
Ryan
Please sign in to rate this answer.
Nibbler 616 Reputation points

2021-02-15T10:14:29.04+00:00

Hi @Ryan Hill

Sorry for the late reply, and thanks for your feedback

I have tried your sample, but just getting error: | where RequestUri_s !in('<not>'.'<this>','<Umbraco'>).

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| where isReceivedFromClient_b == true
| where toint(httpStatusCode_s) >= 500
| extend ParsedUrl = parseurl(requestUri_s)
| where RequestUri_s !in('<not>'.'<this>','<Umbraco'>).
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), StatusCode = httpStatusCode_s
| order by RequestCount desc

Cheers

bharathn-msft 5,086 Reputation points Microsoft Employee

2021-02-24T05:18:52.567+00:00

Hi @KE1980 ,

Apologies in getting back to this thread. Are you still experiencing the issue ? It might have been a typo when @Ryan Hill shared earlier, I have updated the script . Please see if the below script works for you and let us know if you have any further queries. Thank you.

AzureDiagnostics | where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog" | where isReceivedFromClient_b == true | where toint(httpStatusCode_s) >= 500 | where RequestUri_s !in('<not>','<this>','<Umbraco>') | extend ParsedUrl = parseurl(requestUri_s) | summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), StatusCode = httpStatusCode_s | order by RequestCount desc

Ryan Hill 25,661 Reputation points Microsoft Employee

2021-02-24T13:17:56.287+00:00

In addition @KE1980, '<not>','<this>','<word>' were simply place holders that should be replaced by the term(s) you want exclude. My apologies for the confusion. The error might have been caused by < > characters. If you want to omit Umbraco, it would be

| where RequestUri_s !in('/Umbarco').

The / preceeding Umbarco is there because RequestUri is the relative path of the request. If Umbarco is in various places of the relative path, then consider using | where RequestUri_s !contains('Umbarco').

Regards,
Ryan

bharathn-msft 5,086 Reputation points Microsoft Employee

2021-02-24T23:17:32.01+00:00

@KE1980, Checking in to see if you had a chance to review the above information and have any further queries. Thanks

Nibbler 616 Reputation points

2021-02-26T12:45:10.64+00:00

Hi @Ryan Hill and @bharathn-msft

Thanks for you feedback.

I will give it a GO, see if I can get to work / get the desired result and get back to you with a status.

Nibbler 616 Reputation points

2021-02-26T12:50:47.03+00:00

@bharathn-msft

I will give it a GO, see if I can get to work / get the desired result and get back to you with a status.

bharathn-msft 5,086 Reputation points Microsoft Employee

2021-02-28T18:23:50.873+00:00

Thank you @KE1980 for your response , looking forward to hear back from you.

Nibbler 616 Reputation points

2021-03-02T07:54:25.577+00:00

Hi again,

I`m getting the following error when running the script. Have been trying to modifie it, but no luck with that.

Ryan Hill 25,661 Reputation points Microsoft Employee

2021-03-02T14:59:34.77+00:00

RequestUri_s isn't a valid column @KE1980, my apologies for that. Try using requestUri_s instead.

Regards,
Ryan

bharathn-msft 5,086 Reputation points Microsoft Employee

2021-03-04T04:45:58.887+00:00

@KE1980 Checking in to see if you able to try the edit @Ryan Hill suggested and see it works for your scenario and have any further queries. Thank you
Sign in to comment