3

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
Zack ISSOIR
  • 964
  • 11
  • 24

2 Answers2

14

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/

Alexander Sloutsky
  • 2,827
  • 8
  • 13
1

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)

John Gardner
  • 24,225
  • 5
  • 58
  • 76
  • This worked for me as opposed to the accepted answer, where as you say as a table can exist but have no rows (particularly if it was newly-created). – Alpine May 17 '23 at 02:06