Forum Discussion
Solved
Country names in maps
I'm trying to map out the malicious IPs attempting connections to our ASA. It works in Log Analytics with this query:
CommonSecurityLog
| where isnotempty(MaliciousIP)
| summarize count() by MaliciousIPCountry
But when I try to visualise this as a map in a workbook, some of the results appear as unknown including People's Republic of China and Korea. I figure it's because Azure refers to PRC as China maybe. In the Azure SigninLogs, countries use the two letter codes e.g. CN, KR.
Does anyone know how to prevent these showing as Unknown? I can use Latitude and Longitude for the map but it doesn't group co-ordinates by country so there are lots of dots for China instead of one big one. If there is a way to group co-ordinates within one country as a single dot then that would work also.
endakelly It sounds like you know which country codes are causing the issues and what they should be. You could do a mapping in your KQL to fix just those issues if there are not that many using a case statement like:
extend MaliciousCountry = case(MaliciousIPCountry == "PRC, "PR", MaliciousIPCountry == "Korea, "KR", MaliciousIPCountry)
This will do the translation for those countries that need it or else just return the country code if no translation is needed.
(I have no clue what the real country codes should be but hopefully this gets the point across)
- CliveWatson
Microsoft
Hi endakelly
A couple of options
https://techcommunity.microsoft.com/t5/azure-log-analytics/log-analytics-look-up-external-source-of-data/m-p/101198 see my reply here on how to use a country code (I used an external source) but if you have a local Table then you can use that in a Join?
You can also try to reduce the Map data - Top 10 seems to reduce "other" from appearing, I suspect its linked to a line wrap for the legend (assuming you are ok with seeing just the top 10). With my data I see "other" if I use Top 11 onwards...
externaldata(Name:string, Code:string) [@"https://datahub.io/core/country-list/r/data.csv"] | join kind= inner ( CommonSecurityLog | where isnotempty(MaliciousIP) | summarize count() by MaliciousIPCountry ) on $left.Name == $right.MaliciousIPCountry | project Code, count_, MaliciousIPCountry | top 10 by count_I then mapped the Country Code to the location
CliveWatson this works also however the csv you've got has China instead of People's Republic of China so it doesn't work for that but in principle this method would work. You could have your own csv in an Azure Table that would work for this I imagine.
Thanks
- CliveWatson
endakelly It sounds like you know which country codes are causing the issues and what they should be. You could do a mapping in your KQL to fix just those issues if there are not that many using a case statement like:
extend MaliciousCountry = case(MaliciousIPCountry == "PRC, "PR", MaliciousIPCountry == "Korea, "KR", MaliciousIPCountry)
This will do the translation for those countries that need it or else just return the country code if no translation is needed.
(I have no clue what the real country codes should be but hopefully this gets the point across)
GaryBushey works a treat. Slight syntax error in what you've got here but I was able to correct it.
CommonSecurityLog| where isnotempty(MaliciousIP)| extend MaliciousIPCountry = case(MaliciousIPCountry == "People's Republic of China", "CN", MaliciousIPCountry == "Korea", "KR", MaliciousIPCountry)| summarize count() by MaliciousIPCountryThanks
