Forum Discussion

endakelly's avatar
endakelly
Brass Contributor
Mar 12, 2020
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.
  • GaryBushey's avatar
    GaryBushey
    Mar 13, 2020

    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)

5 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Mar 13, 2020

    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 

     

     

    • endakelly's avatar
      endakelly
      Brass Contributor
      Mar 16, 2020

      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

  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor
    Mar 13, 2020

    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)

    • endakelly's avatar
      endakelly
      Brass Contributor
      Mar 16, 2020

      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 MaliciousIPCountry 
       
      Thanks

Resources