SOLVED

Country names in maps

Highlighted
Contributor

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.
5 Replies
Highlighted
Best Response confirmed by endakelly (Contributor)
Solution

@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)

Highlighted

Hi @endakelly 

 

A couple of options

 

https://techcommunity.microsoft.com/t5/azure-log-analytics/log-analytics-look-up-external-source-of-...  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 

 

Annotation 2020-03-13 115735.png

 

Highlighted

Hi @endakelly 

 

I just noticed 10 is a Map setting - so you can change "others"

 

Annotation 2020-03-14 094508.jpg

Highlighted

@Gary Bushey 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 MaliciousIPCountry 
 
Thanks
Highlighted

@Clive Watson 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