CODEWITHSUNDEEP
azure-data-explorerkql
PankajSanwal
PankajSanwal

Reputation: 1019

Passing table list to "Find In" operator dynamically at run time in Kusto Query Language

I have a where condition which I want to run over a set of tables in my Azure Data Explorer DB. I found "Find in ()" operator in Kusto query quite useful, works fine when I pass list of tables as intended.

find withsource=DataType in (AppServiceFileAuditLogs,AzureDiagnostics) 
where TimeGenerated > ago(31d) 
project _ResourceId, _BilledSize, _IsBillable
| where _IsBillable == true
| summarize BillableDataBytes = sum(_BilledSize) by _ResourceId, DataType | sort by BillableDataBytes nulls last

However, in my scenario, I would like to decide the list of tables at run time using another query.

Usage
| where TimeGenerated > ago(32d)
| where StartTime >= startofday(ago(31d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000 by DataType
| sort by BillableDataGB desc
|project DataType

find withsource=DataType in (<pass resulting table expression from above query here as comma separated list of tables>) 
where TimeGenerated > ago(31d) 
project _ResourceId, _BilledSize, _IsBillable
| where _IsBillable == true
| summarize BillableDataBytes = sum(_BilledSize) by _ResourceId, DataType | sort by BillableDataBytes nulls last

Found some examples of passing all tables in a database or cluster using wildcards but that does not fit my scenario. Can somebody help me here.

Upvotes: 0

Views: 1695

Answers (1)

Avnera
Avnera

Reputation: 7608

Here is one way to achieve this:

let Tables = toscalar(Usage
| where TimeGenerated > ago(32d)
| where StartTime >= startofday(ago(31d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize by DataType);
union withsource=T *
| where T in (Tables)
| count

Note that there is a significance to the toscalar expression, it precalculates the list of tables and optimizes the filter on the union expression. I also updated your query to avoid unnecessary work.

Upvotes: 1

Related Questions