Azure Log Analytics: how to read a file

I often blog about various Log Analytics syntax after I get asked the same question a few times, in this case a few times last month and twice this week so far!

Also posted as a reply here

_______________________________________________________________________________________________________________________________________

You can use externaldata operator to read files, like csv or tsv, scsv, sohsv, psv, txt, raw.

This example .CSV file happens to be publicly accessible on a website, but you could use one location on Azure Blob storage instead? This one line is all you need to run in Log Analytics to get the file content. As you can see you do need to know and name which column names to return from the file, Name and Code in the example.


externaldata(Name:string, Code:string) [@"https://datahub.io/core/country-list/r/data.csv"]


Go to Log Analytics and Run Query

Example results

Name Code
Afghanistan AF
Åland Islands AX
Albania AL
Algeria DZ

You can then JOIN those datasets together – I’ve used the WireData table, but you may need to use one you have or one you want to use – the link below uses the Microsoft demo dataset?

externaldata(Name:string, Code:string) [@"https://datahub.io/core/country-list/r/data.csv"]
| join kind= inner (
WireData
| where TimeGenerated > startofday(ago(1d))
) on $left.Name == $right.RemoteIPCountry


The above query is useful, as while the WireData table has the full country name (e.g. United States) it doesn’t have the Country Code (e.g. US). You can use this data to enrich your own data with external data kept in files.

Please run the above in the demo workspace, which adds the CODE column from the .csv file to the WireData information – just use this link:
Go to Log Analytics and Run Query

Example Results