Mar 12 2020 03:36 PM
I'm trying to map out the malicious IPs attempting connections to our ASA. It works in Log Analytics with this query:
Mar 13 2020 04:51 AM - edited Mar 13 2020 04:53 AM
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)
Mar 13 2020 05:12 AM
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
Mar 14 2020 02:47 AM
Mar 16 2020 04:45 AM
@Gary Bushey works a treat. Slight syntax error in what you've got here but I was able to correct it.
Mar 16 2020 04:46 AM
@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
Mar 13 2020 04:51 AM - edited Mar 13 2020 04:53 AM
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)