Using KQL and externaldata() operator to pull infromation from json file

Question

Trying to create a sentinel query (KQL) which uses the externaldata() operator to ingest the information from the json file 'https://www.gov.uk/bank-holidays.json'. Problems I am finding is due to this json file containing the column / field 'date' sentinel does not allow this as a variable. Anyone been able to get a multilayer json fields from an external file?

externaldata (title:string, date:string, notes:string, bunting:bool)[
    @"https://www.gov.uk/bank-holidays.json"
    ]
    with(format="multijson")

David דודו Markovitz · Accepted Answer · 2022-09-17T17:30:29.547

The externaldata operator was created to enable users of Azure Data Explorer (AKA Kusto) based SaaS systems, such as Log Analytics and Application Insights, to work with external data located in Azure storage.
Retrieving data from web sites in an unsupported scenario.
Sometimes it works, and sometimes not (depends on what lies on the other side).
For your specific URL, it does not work.
Special names in KQL can be expressed with bracket and single/double qualifies, e.g., ['date'] or ["date"]
The entire document is written in a single row. json is enough. No need for multijson.
The assumed schema is wrong (title:string, date:string, notes:string, bunting:bool).
The JSON has 3 keys in the 1st layer, one for each kingdom: "england-and-wales", "scotland" & "northern-ireland".
While we can use the above keys to read the JSON, I would prefer reading it as txt or raw, parsing it to JSON and then explode it, as demonstrated in the query below.

externaldata(doc:string)
[h'https://<storage-account-name>.blob.core.windows.net/mycontainer/bank-holidays.json;<secret>']
with(format='txt')
| project parse_json(doc)
| mv-expand kind=array doc
| project   kingdom  = tostring(doc[0])
           ,division = doc[1].division
           ,events   = doc[1].events
| mv-expand events
| evaluate bag_unpack(events) 
//| sample 10

kingfom	division	bunting	date	notes	title
northern-ireland	northern-ireland	false	2017-04-14T00:00:00Z		Good Friday
england-and-wales	england-and-wales	true	2017-05-29T00:00:00Z		Spring bank holiday
scotland	scotland	false	2018-03-30T00:00:00Z		Good Friday
england-and-wales	england-and-wales	true	2018-12-25T00:00:00Z		Christmas Day
northern-ireland	northern-ireland	false	2019-04-19T00:00:00Z		Good Friday
england-and-wales	england-and-wales	true	2019-12-25T00:00:00Z		Christmas Day
northern-ireland	northern-ireland	true	2020-01-01T00:00:00Z		New Year’s Day
scotland	scotland	true	2022-01-04T00:00:00Z	Substitute day	2nd January
scotland	scotland	false	2022-09-19T00:00:00Z		Bank Holiday for the State Funeral of Queen Elizabeth II
scotland	scotland	true	2023-01-02T00:00:00Z	Substitute day	New Year’s Day

Using KQL and externaldata() operator to pull infromation from json file

1 Answers1