Kusto nested let statement

246 views Asked by At

Is there a way to have a nested let statement in Kusto (KQL)? The solution below didn't work for me.

let GetData = (ReportingDatabase: string, IdleDetectDatabase: string, ScaleUnit: string)
{
let wv_entity = cluster(ReportingDatabase).database('Report').WorkspaceEntity_MV;
let dv_entity = cluster(ReportingDatabase).database('Report').DeviceEntity_MV;
let TimeScale = "7d";
let unique_devices = (
    cluster(IdleDetectDatabase).database("idledetect").DailyHibernateResumeComparisonByDevice_Snapshot
    | where UsageDate > ago(totimespan(TimeScale))
    | where IsPredicted == true);
    let TimeScale_7 = 
        cluster(IdleDetectDatabase).database("idledetect").DailyRDAgentHROutofwindowLoginPerDevice_Snapshot
        | where UsageDate > ago(totimespan(TimeScale))
        | summarize 
            day_1= countif(device_count >=1),
            day_2= countif(device_count >=2),
            day_3= countif(device_count >=3),
            day_4 = countif(device_count >=4),
            day_5 = countif(device_count >=5),
            day_6 = countif(device_count >=6),
            day_7 = countif(device_count >=7);
    TimeScale_7
};
union
    GetData('clusterna01.eastus.kusto.windows.net', 'clusterna01.eastus.kusto.windows.net', 'PRNA01'),
    GetData('clusterna02.centralus.kusto.windows.net', 'clusterna02.centralus.kusto.windows.net', 'PRNA02'),
    GetData('clustereu01.northeurope.kusto.windows.net', 'clustereu01.northeurope.kusto.windows.net', 'PREU01'),
    GetData('clustereu02.westeurope.kusto.windows.net', 'clustereu02.westeurope.kusto.windows.net', 'PREU02'),
    GetData('clusterap01.southeastasia.kusto.windows.net', 'clusterap01.southeastasia.kusto.windows.net', 'PRAP01'),
    GetData('clusterau01.australiaeast.kusto.windows.net', 'clusterau01.australiaeast.kusto.windows.net', 'PRAU01')

I am asking that, because "inside" the TimeScale_7 I can change the query in any way I want and then use the union operator later.

So, I would like to merge the results of the query above with the SAME query, but the summarization will be like the one below, when let TimeScale = "15d";, for example:

| summarize 
    day_1= countif(device_count >=1),
    day_2= countif(device_count >=2),
    day_3= countif(device_count >=3),
    day_4 = countif(device_count >=4),
    day_5 = countif(device_count >=5),
    day_6 = countif(device_count >=6),
    day_7 = countif(device_count >=7),
    day_8 = countif(device_count >=8),
    day_9 = countif(device_count >=9),
    day_10 = countif(device_count >=10),
    day_11 = countif(device_count >=11),
    day_12 = countif(device_count >=12),
    day_13 = countif(device_count >=13),
    day_14 = countif(device_count >=14),
    day_15 = countif(device_count >=15);

The same will happen for "30d", the summarization will increase. So at the end, I want ONE output. But I cannot add the TimeScale in the GetData function because the summarization needs to be calculated as example above.

1

There are 1 answers

1
Rakesh Govindula On

Your query will give error because the unique_devices was not declared.

enter image description here

As per the Documentation,

Nested let statements are not supported like yours. You can use scalar value defined using one let in another let like below.

let TimeScale = "700d";
let TimeScale_30 = a| where UsageDate > ago(totimespan(TimeScale));
TimeScale_30

enter image description here

You can change the query in second let statement as per your requirement and if you want to use union, you can use it on the TimeScale_30 which is a table.