External Data / Watchlist enrichment help

Question

I've tried a lot of different solutions and can't quite seem to get it.

I want to enrich an alert I have created where we get notified that a user has logged in outside of my organisations country of operations.

The AAD logs provide that location, however only the country code (FR, DE, US, AU) rather than the full country name (France, Germany, United States, Australia). The AAD do show City / State details as well, but that is mostly useless in this case.

I want to enrich my data by using either a Sentinel Watchlist or an externally hosted .csv file, so that it looks up the country code and projects it in to the results of the query so I can map this in to an entity and then, finally, pass it through a Playbook.

I would like my results to look similar to this:

UserPrincipalName,Location,Country-Pulled-From-CSV,AppDisplayName
bob@domain.com,CA,Canada,Windows Signin
alice@domain.com,FR,France,Microsoft Teams

I can't, however, seem to get the data to join on my results. Any

External/Watchlist data is in .csv format with the following headings.

ccTLD,Country
FR,France
DE,Germany
US,United States of America
AU,Australia
IT,Italy
CA,Canada
JP,Japan

My query, at the moment, is below

let countryCodes = externaldata(ccTLDs:string, Country:string )[@"https://raw.githubusercontent........cctlds.csv"] with (format="csv", ignoreFirstRecord=True);
SigninLogs
| where Location != "US"
| join kind=leftouter countryCodes on $left.Location == $right.ccTLDs
//| lookup countryCodes on $left.Location == $right.ccTLDs
| project UserPrincipalName, Location, Country

I have tried every kind of join and lookups and can't get this to work how I want it. Any ideas?

Answer 1

Figured it out, I had 2 issues.

1) Forgot to remove the leading dot from the table of ccTLDs I sourced from Wikipedia, so the table was actually:

 ccTLD,Country
 .fr,France
 .de,Germany
 .us,United States of America
 .au,Australia
 .it,Italy
 .ca,Canada
 .jp,Japan

2) ccTLDs from the wikipedia table were also in lowercase

Once I realised this mistake the following line was able to enrich my data in the manner anticipated

| join kind = leftouter countryCodes on $left.Location == $right.ccTLDs