Search code examples
azurekql

Create a permanant table in Azure moniotr KQL enviorment

I want to create a permanent table in azure monitor KQL environment. Are clients have informal and formal names and I need to drag back the formal name and normal Id just create a table

Yes I know I could get a repeating SQL to send it daily but this seems super silly method

I want to send it once and just say there - are there any suggestions?

Solution

  • You could use the externaldata functionality here.

    There are some considerations though. If this table is confidential you may want to host it in a storage account with a SAS key. If you expect this table to update often you may not be able to get away from some sort of automation as you allude to.

    If this list is relatively static something like this could work but is open to the world:

    let CustomerLookupTable = externaldata(ID:int, InformalName:string, FormalName:string) [h'https://firewalliplists.gypthecat.com/lists/stackoverflowexamples/customers.csv'] with (ignoreFirstRecord=true);
CustomerLookupTable

    Or same example with a fictitious SAS key:

    let CustomerLookupTable = externaldata(ID:int, InformalName:string, FormalName:string) [h'https://example.blob.core.windows.net/customerdata/customers.csv?sv=2023-01-03&st=2025-02-15T13%abcdefghijklmnopqrstuvwxyz'] with (ignoreFirstRecord=true);
CustomerLookupTable
    ID InformalName FormalName
    273 Contoso Contoso Banking Corporation
    352 Fabrikam Fabrikan, Inc
    376 TailSpin TailSpin Toys and Partners
    421 WingTip WingTip Toys plc