Home

Query to know if table exists in LA workspace

%3CLINGO-SUB%20id%3D%22lingo-sub-807810%22%20slang%3D%22en-US%22%3EQuery%20to%20know%20if%20table%20exists%20in%20LA%20workspace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807810%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20query%26nbsp%3B%20LA%20workspace(s)%20and%20the%20query%20will%20include%20couple%20of%20tables.%3CBR%20%2F%3EThe%20LA%20workspace%20need%20not%20have%20all%20tables%20and%20hence%20I%20need%20to%20something%20like%20tableifExists%20and%20if%20true%2C%20query%20the%20table.%20(Something%20like%20columnifexists).%3CBR%20%2F%3EAfter%20using%20tableIfExists%20for%20all%20table%2C%20I%20need%20to%20do%20union%20of%20all%20table%20and%20fetch%20the%20results.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20please%20help%20me%20with%20the%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-807810%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808046%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20know%20if%20table%20exists%20in%20LA%20workspace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808046%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F263110%22%20target%3D%22_blank%22%3E%40Vino55%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3EI%20do%20not%20think%20this%20is%20possible.%20It%20is%20like%20you%20are%20trying%20to%20avoid%20exposing%20errors%20due%20to%20not%20only%20specific%20data%20being%20there%20but%20any%20data.%20What%20is%20the%20scenario%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809895%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20know%20if%20table%20exists%20in%20LA%20workspace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809895%22%20slang%3D%22en-US%22%3EYou%20can%20use%20isfuzzy%20parameter%20in%20union%20clause.%20See%20the%20example%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Funionoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Funionoperator%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%2F%2F%20Using%20union%20isfuzzy%3Dtrue%20to%20access%20non-existing%20view%3A%20%3CBR%20%2F%3Elet%20View_1%20%3D%20view%20()%20%7B%20print%20x%3D1%20%7D%3B%3CBR%20%2F%3Elet%20View_2%20%3D%20view%20()%20%7B%20print%20x%3D1%20%7D%3B%3CBR%20%2F%3Elet%20OtherView_1%20%3D%20view%20()%20%7B%20print%20x%3D1%20%7D%3B%3CBR%20%2F%3Eunion%20isfuzzy%3Dtrue%3CBR%20%2F%3E(View_1%20%7C%20where%20x%20%26gt%3B%200)%2C%20%3CBR%20%2F%3E(View_2%20%7C%20where%20x%20%26gt%3B%200)%2C%3CBR%20%2F%3E(View_3%20%7C%20where%20x%20%26gt%3B%200)%3CBR%20%2F%3E%7C%20count%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814996%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20know%20if%20table%20exists%20in%20LA%20workspace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814996%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECurrently%20most%20of%20the%26nbsp%3B%20RP%20data%20(who%20are%20not%20onboarded%20to%20resource%20specific)%20move%20to%20AzureDiagnostics.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20are%20moving%20to%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fdiagnostic-logs-stream-log-store%23azure-diagnostics-vs-resource-specific%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EResource%20specific%20feature%3C%2FA%3Eand%20due%20to%20which%20we%20are%20creating%20workflow%20in%20LA.%3CBR%20%2F%3EBy%20this%2C%20LA%20workspace%20will%20always%20have%20our%20table%20with%20defined%20schema.%3C%2FP%3E%0A%3CP%3EHence%20this%20table%20will%20always%20exist%20in%20LA%20workspace%20when%20we%20query.%20if%20there%20is%20no%20data%2C%20LA%20is%20going%20to%20return%200%20records%20but%20not%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20the%20workspace%20may%20or%20may%20not%20have%20table%20AzureDiagnostics.%20You%20can%20quickly%20check%20by%20creating%20a%20new%20workspace%20and%20you%20will%20not%20find%20table%20AzureDiagnostics.%20If%20there%20is%20no%20data%2C%20since%20table%20will%20not%20exist%2C%20LA%20will%20return%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20our%20intent%20to%20combine%20data%20from%20both%20table%20(AzureDiagnostics%20and%20our%20new%20table)%20from%20our%20workbook%2C%20our%20customer%20workspace%20may%20or%20may%20not%20have%20AzureDiagnostic%20table.%20Hence%20we%20need%20to%20handle%20this%20in%20our%20query%20used%20in%20our%20workbook%20template.%3CBR%20%2F%3EI%20will%20try%20to%20follow%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54749%22%20target%3D%22_blank%22%3E%40Ketan%20Ghelani%3C%2FA%3E's%20suggestion.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Vino55
Microsoft

I need to query  LA workspace(s) and the query will include couple of tables.
The LA workspace need not have all tables and hence I need to something like tableifExists and if true, query the table. (Something like columnifexists).
After using tableIfExists for all table, I need to do union of all table and fetch the results.

Could you please help me with the query?

3 Replies

Hi @Vino55,

I do not think this is possible. It is like you are trying to avoid exposing errors due to not only specific data being there but any data. What is the scenario here?

You can use isfuzzy parameter in union clause. See the example

https://docs.microsoft.com/en-us/azure/kusto/query/unionoperator

// Using union isfuzzy=true to access non-existing view:
let View_1 = view () { print x=1 };
let View_2 = view () { print x=1 };
let OtherView_1 = view () { print x=1 };
union isfuzzy=true
(View_1 | where x > 0),
(View_2 | where x > 0),
(View_3 | where x > 0)
| count

Hi @Stanislav Zhelyazkov ,

 

Currently most of the  RP data (who are not onboarded to resource specific) move to AzureDiagnostics.

 

We are moving to Resource specific feature and due to which we are creating workflow in LA.
By this, LA workspace will always have our table with defined schema.

Hence this table will always exist in LA workspace when we query. if there is no data, LA is going to return 0 records but not error.

 

However, the workspace may or may not have table AzureDiagnostics. You can quickly check by creating a new workspace and you will not find table AzureDiagnostics. If there is no data, since table will not exist, LA will return error.

 

As our intent to combine data from both table (AzureDiagnostics and our new table) from our workbook, our customer workspace may or may not have AzureDiagnostic table. Hence we need to handle this in our query used in our workbook template.
I will try to follow @Ketan Ghelani 's suggestion.