Quick tip – Country Codes

All countries has an ISO code, described in ISO 3166 is an international standard.
These codes are used throughout the IT industry by computer systems and software to make it easier to identify a country.

It has multiple formats and they country codes are presented in the following formats: Alpha-2 (2 characters), Alpha-3 (3 characters) and Numeric (3 digits).

In the data from some logs like SigninLogs and IdentityLogonEvents the country is presented as Alpha-2. We realized pretty quick is that some 2-characters country codes are difficult to remember. As in below, picture it could be difficult to know all these countries.

We have been using this for a long time and thought it might be something others can use as well.

So to solve this I created a csv file and placed on github:

https://raw.githubusercontent.com/mattiasborg82/Hunting/main/General/cc.txt

To be able to join our data with this file we can use the external data operator in Kusto

Since it’s a CSV file, we can make it more usable by split the rows on comma

To to build the full use-case for this, we join it with our SigninLogs (or other logs that uses the country code)

Copy friendly code

let CountryCodes = externaldata (CountryCode:string)
[
 @"https://raw.githubusercontent.com/mattiasborg82/Hunting/main/General/cc.txt"
]
with(ignoreFirstRecord=true);
SigninLogs
| where isnotempty(Location)
| join kind=leftouter (
    CountryCodes
    | extend Country = tostring(split(CountryCode, ",")[0]),
              Location = tostring(split(CountryCode, ",")[1])
    | project-away CountryCode)
on Location
| summarize count() by Country,UserDisplayName

This can be used further to combine with conditional access blocks showing potential credential leak

Happy Hunting!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.