CODEWITHSUNDEEP
azurekql
Symon Turner
Symon Turner

Reputation: 175

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?

Upvotes: 0

Views: 68

Answers (2)

Gyp the Cat
Gyp the Cat

Reputation: 721

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

Upvotes: 1

Jahnavi
Jahnavi

Reputation: 8018

In Azure Monitor, it is not possible to create permanent tables unlike in SQL. Instead, you can create a custom table under Log Analytics which can be used for sending and retrieving logs and can also apply all the relevant data policies or retention periods to it according to the requirement.

Refer MSDoc for the same detailed information.

To create a custom table, follow the below process.

Go to Monitor >> Log Analytics Workspaces >> Tables >> Create

enter image description here

Now, choose custom log (Dcr-based) table which set up data collection rules for data ingestion. Once, it is done provide the custom table and the relevant DCR details (existed or create new one) and now provide a sample data of logs in the schema column as shown.

enter image description here

The custom table has been created successfully in the below way.

enter image description here

Note: You can be able to edit the schema and also manage the table properties or fields accordingly even after the table creation.

Upvotes: 0

Related Questions