jsonazurekqlazure-sentinel

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


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

Solution

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