CODEWITHSUNDEEP
azure-log-analyticsazure-data-explorer
Zack ISSOIR
Zack ISSOIR

Reputation: 974

Check if a table exists in Kusto language?

Is there a way to programmatically check if a table is existent in log analytics using the kusto language?

For example, let's suppose I want to check if a workspace contains the VMConnection table something like : 

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL

OR 

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Upvotes: 4

Views: 5336

Answers (2)

John Gardner
John Gardner

Reputation: 25116

Alternate answer with results:

let MissingTable = view () { print isMissing=1 };
union isfuzzy=true MissingTable, (AzureDiagnostics | getschema | summarize c=count() | project isMissing=iff(c > 0, 0, 1)) 
| top 1 by isMissing asc

This query returns a 1 if the AzureDiagnostics table doesn't exist in the cluster. If the real table doesn't exist, the fake row of the MissingTable will be returned. If any columns exist in the schema for the AzureDiagnostics table, a 0 is returned.

if you don't care if the table doesn't exist and just want 0 rows, instead of going through getschema, you could just make the view have the columns you would have gotten in your query but just have no rows in that view/datatable:

let MissingTable = datatable(ResourceId: string) [];
union isfuzzy=true MissingTable, (AzureDiagnostics
| extend ResourceId = column_ifexists('ResourceId', '')

In this case, the query returns no rows if the AzureDiagnostics table is missing, or if the ResourceId column is missing from the table.

depending on where you are you might get a query warning indicator that tables were missing, that's expected behavior with fuzzy unions.

(also at https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-create-workbook#best-practices-for-querying-logs)

Upvotes: 2

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

Perhaps, you can use next technique to check if there is non-empty table present:

let hasNonEmptyTable = (T:string) 
{ 
   toscalar( union isfuzzy=true ( table(T) | count as Count ), (print Count=0) | summarize sum(Count) ) > 0
};
let TableName = 'StormEvents';
print Table=TableName, IsPresent=iif(hasNonEmptyTable(TableName), "Table present", "Table not preset")

You can try running it online using https://dataexplorer.azure.com/clusters/help/

Upvotes: 15

Related Questions