Want to get the top 10 countries for the most requests received to application gateway

Question

Want to get the top 10 countries for the most requests received to application gateway

Rajith pathiraja 21

hi,

Im designing a security dashboard for quick reference for my azure application gateway. i want to have check the top 10 received requests by sorted by the country of traffic origin.
How can i write a query for that ?

i tried below but want working ?

AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess"
| extend clientIP_geo = geoip(clientIP_s)
| summarize AggregatedValue = count() by clientIP_s, country = clientIP_geo.Country
| top 10 by AggregatedValue

Luke Murray 11,436 Reputation points MVP Volunteer Moderator

2023-01-06T05:13:42.667+00:00

I don't have a GeoIP function, but try this:

AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| extend clientIp_s
| summarize AggregatedValue = count() by clientIp_s
| extend country = geoip(clientIp_s).country_name
| top 10 by AggregatedValue
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2023-01-06T12:10:47.86+00:00

Hello @Rajith pathiraja ,

Welcome to Microsoft Q&A Platform. Thank you for reaching out & hope you are doing well.

AFAIK, it is not possible to get the top 10 countries for the most requests received by application gateway.

If you look into the Application gateway Access log or Firewall Log values, you will only find "clientIP" as below:

Refer : https://learn.microsoft.com/en-us/azure/application-gateway/application-gateway-diagnostics#access-log

So, there is no way to summarize the logs by country information.

Summarizing logs by country information is only possible where the logs have a value called "clientCountry". For example Application Insights logs called "AppRequests".
Refer : https://learn.microsoft.com/en-us/azure/azure-monitor/app/convert-classic-resource#appavailabilityresults
You can find a value called "client_CountryOrRegion" in this log.

For Application gateway, you can only get Top 10 Client IPs as mentioned in the below doc:
https://learn.microsoft.com/en-us/azure/application-gateway/monitor-application-gateway#sample-kusto-queries

Regards,
Gita
Rajith pathiraja 21 Reputation points

2023-01-07T02:34:12.827+00:00

hi @Luke Murray thanks for the quick respond. The query you provide isn't working. its giving error "Unknown function: 'geoip'."
Rajith pathiraja 21 Reputation points

2023-01-07T02:35:30.143+00:00

hi @GitaraniSharma-MSFT , Thanks for the detail explanation.
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2023-01-07T09:01:38.063+00:00

Hello @Rajith pathiraja ,

Happy to help!

I've added the answer below for broader visibility.

If you have any further questions, please add comments below. If there are no additional questions and the provided information helped you, please "Accept the answer" as this will help us and others in the community as well.

Regards,
Gita

Accepted answer

0 additional answers

Your answer

Luke Murray 11,436 Reputation points MVP Volunteer Moderator

2023-01-06T05:13:42.667+00:00

I don't have a GeoIP function, but try this:

AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| extend clientIp_s
| summarize AggregatedValue = count() by clientIp_s
| extend country = geoip(clientIp_s).country_name
| top 10 by AggregatedValue
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2023-01-06T12:10:47.86+00:00

Hello @Rajith pathiraja ,

Welcome to Microsoft Q&A Platform. Thank you for reaching out & hope you are doing well.

AFAIK, it is not possible to get the top 10 countries for the most requests received by application gateway.

If you look into the Application gateway Access log or Firewall Log values, you will only find "clientIP" as below:

Refer : https://learn.microsoft.com/en-us/azure/application-gateway/application-gateway-diagnostics#access-log

So, there is no way to summarize the logs by country information.

Summarizing logs by country information is only possible where the logs have a value called "clientCountry". For example Application Insights logs called "AppRequests".
Refer : https://learn.microsoft.com/en-us/azure/azure-monitor/app/convert-classic-resource#appavailabilityresults
You can find a value called "client_CountryOrRegion" in this log.

For Application gateway, you can only get Top 10 Client IPs as mentioned in the below doc:
https://learn.microsoft.com/en-us/azure/application-gateway/monitor-application-gateway#sample-kusto-queries

Regards,
Gita
Rajith pathiraja 21 Reputation points

2023-01-07T02:34:12.827+00:00

hi @Luke Murray thanks for the quick respond. The query you provide isn't working. its giving error "Unknown function: 'geoip'."
Rajith pathiraja 21 Reputation points

2023-01-07T02:35:30.143+00:00

hi @GitaraniSharma-MSFT , Thanks for the detail explanation.
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2023-01-07T09:01:38.063+00:00

Hello @Rajith pathiraja ,

Happy to help!

I've added the answer below for broader visibility.

If you have any further questions, please add comments below. If there are no additional questions and the provided information helped you, please "Accept the answer" as this will help us and others in the community as well.

Regards,
Gita

Answer 1

Hello @Rajith pathiraja ,

I understand that you want to get the top 10 countries for the most requests received by your Application gateway.

However, this is not possible as of today.

If you look into the Application gateway Access log or Firewall Log values, you will only find "clientIP" as below:

Refer : https://learn.microsoft.com/en-us/azure/application-gateway/application-gateway-diagnostics#access-log

So, there is no way to summarize the logs by country information.

Summarizing logs by country information is only possible where the logs have a value called "clientCountry". For example Application Insights logs called "AppRequests".
Refer : https://learn.microsoft.com/en-us/azure/azure-monitor/app/convert-classic-resource#appavailabilityresults
You can find a value called "client_CountryOrRegion" in this log to summarize your logs by this function/value and can render the data using a area/bar/column/pie/scatter/table/time/treemap chart as below:

For Application gateway, you can only get Top 10 Client IPs as shown in my previous screenshot and mentioned in the below doc:
https://learn.microsoft.com/en-us/azure/application-gateway/monitor-application-gateway#sample-kusto-queries

Kindly let us know if the above helps or you need further assistance on this issue.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Share via

Want to get the top 10 countries for the most requests received to application gateway

0 additional answers

Your answer