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")

CodePudding user response：

1. 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.
2. Special names in KQL can be expressed with bracket and single/double qualifies, e.g., ['date'] or ["date"]
3. The entire document is written in a single row. json is enough. No need for multijson.
4. 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".
5. 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