Conditional Result - Azure Data Explorer / Log Analytics / KQL

Question

Currently, I am having a query which returns Union of 3 tables (total 13 rows). All 3 tables have same set of columns.

Current Query

let bytes_to_gb =
    (1024 * 1024 * 1024)
    ;
let tab_cpu =
    performanceCounters
    | where category == "Processor" and counter == "% Processor Time" and instance == "_Total"
    | where ...
    | summarize timestamp = max(timestamp), value = avg(value) by host_name = cloud_RoleInstance, host_type = "WXYZ", counter_name = "%CPU", threshold = 90
    ;
let tab_memory =
    performanceCounters
    | where category == "Memory" and counter == "Available Bytes"
    | where ...
    | summarize timestamp = max(timestamp), value = avg(value / bytes_to_gb) by host_name = cloud_RoleInstance, host_type = "ZYXW", counter_name = "Available Memory (GB)", threshold = 10
    ;
let tab_exceptions =
    exceptions
    | where ...
    | summarize timestamp = max(timestamp), value = (count(itemCount) * 1.0) by host_name = "Exceptions", host_type = "Web", counter_name = "Exception", threshold = 10
    | where value >= 10
union
    tab_cpu,        // 6 rows
    tab_memory,     // 6 rows
    tab_exceptions  // 1 row

What I are looking for is - include result of tab_cpu and tab_memory ONLY if tab_exceptions has rows.

This is how I would have done in SQL Query, but not getting proper solution for KQL.

IF EXISTS (SELECT * FROM tab_exceptions WHERE ...)
    SELECT * FROM tab_cpu WHERE ...;
    UNION
    SELECT * FROM tab_memory WHERE ...
    UNION
    SELECT * FROM tab_exceptions WHERE ...
ELSE
    ...

Can you please update the SQL sample to include the full 'where' clauses? — Avnera
@Avnera - the resolution I am looking for is for KQL and not for SQL.SQL query is just a sample for better understanding on what resolution I am looking for in KQL. — Indar

Yoni L. Yoni L. · Accepted Answer · 2020-04-21T14:42:30

you could use the union operator in a way similar to the example below:

let T1 = range x from 1 to 3 step 1; // for the other case, replace with: let T1 = datatable(x:long)[];
let T2 = range x from 4 to 6 step 1;
let T3 = range x from 7 to 9 step 1;
let T1_has_rows = toscalar(T1 | summarize count() > 0);
union
(T1 | where T1_has_rows == false),
(union T1, T2, T3 | where T1_has_rows == true)

Conditional Result - Azure Data Explorer / Log Analytics / KQL

Current Query

1 Answers