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?