Azure Application Insights Kusto Language Summurize by where TimeGenerated Value

Question

Is there a way to have the where clausule inside a different column with Kusto Language. I am aware of the "Pivot" syntax that is also used with SQL to create columns based on unique value. But don't think this will help in my case. There is also another SO question who almost has the same question as me. But his solution Didn't work either.

Context of my query: This query gets the runtime of each machine for every month. You may be wondering why I used such a long query to achieve this. Any opnions and adjustments are welcome. I am very new to the language. And I already use the top query to get the start and stop times of each VM in another project.

Original query :

AzureActivity
| where ResourceProvider == "Microsoft.Compute"
and ActivityStatus == "Succeeded"
and OperationName == "Deallocate Virtual Machine"
| project DeallocateResource=Resource
,DeallocatedDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd')
,DeallocatedTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
| join kind=fullouter (AzureActivity
| where ResourceProvider == "Microsoft.Compute"
and ActivityStatus == "Succeeded"
and OperationName == "Start Virtual Machine"
| project StartupResource=Resource
,StartDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd')
,StartTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
) on $right.StartupResource == $left.DeallocateResource
| where StartDate == DeallocatedDate
| project Resource=coalesce(StartupResource, DeallocateResource) ,
Runtime = round(todouble(datetime_diff('minute', todatetime(strcat(StartDate , " " , DeallocatedTime )) , todatetime(strcat(StartDate , " " , StartTime )))) / 60)
| summarize sum(Runtime) by Resource

Now the query above will get the sum of the running time with the time range you specifically set in the portal. To get the sum of the running time for each month (Log analytics is set for 90 days so 3 months ago) I add these where statements. in 3 Different queries. The work gets done And I got 3 different tables with the running time of each month being (month1, month2, month3 ).

| where TimeGenerated > ago(30d)
| where TimeGenerated between(ago(30d) .. ago(60d) )
| where TimeGenerated between(ago(60d) .. ago(90d) )

But these are 3 different queries and 3 different tables. My Goal is to get this look where you have the 3 different (timegenerated where statements inside one Table)

Tried the SO question solution but that didn't go as planned (got an Failed to resolve scalar expression named 'TimeGenerated' error while adding these lines of code to my original query)

| summarize sum(Runtime) by Resource , bin(TimeGenerated, 1m)
| summarize Fistmonth = TimeGenerated > ago(30d),  
            SecondMonth = TimeGenerated between(ago(30d) .. ago(60d)) ,
            ThirdMonth = Runtime_,TimeGenerated between(ago(60d) .. ago(90d) ) by Resource

Does anyone knows what I am missing or overseeing here. Is this possible with kusto ? And do I use an overhead of query for something that can be done in a couple of lines.

score 1 · Answer 1 · answered Mar 28 '19 at 15:27

if I understand your scenario correctly, you could potentially achieve that using sumif, assuming you know the months you're targeting in advance.

Here's an example:

datatable(Resource:string, Runtime:double, TimeGenerated:datetime)
[
    "A", 13.4, datetime(2019-01-01 11:11:11),
    "B", 1.34, datetime(2019-01-01 10:10:10),
    "C", 0.13, datetime(2019-01-01 12:12:12),
    "A", 12.4, datetime(2019-02-01 11:11:11),
    "B", 1.24, datetime(2019-02-01 09:09:09),
    "B", 2.24, datetime(2019-02-01 09:10:09),
    "B", 3.24, datetime(2019-02-01 09:11:09),
    "C", 0.12, datetime(2019-02-01 08:08:08),
    "A", 14.4, datetime(2019-03-01 07:07:07),
    "B", 1.44, datetime(2019-03-01 05:05:05),
    "C", 0.14, datetime(2019-03-01 06:06:06),
]
| summarize Month1 = sumif(Runtime, TimeGenerated between(datetime(2019-01-01)..datetime(2019-02-01))),
            Month2 = sumif(Runtime, TimeGenerated between(datetime(2019-02-01)..datetime(2019-03-01))),
            Month3 = sumif(Runtime, TimeGenerated between(datetime(2019-03-01)..datetime(2019-04-01))) 
         by Resource

I used folowing query based on your advice. https://paste.ee/p/3yIez But Because Timegenerated was not a column , kusto gave me following error. `'summarize' operator: Failed to resolve column or scalar expression named 'TimeGenerated'` Any idea how I can avoid this ? — achahbar, Mar 28 '19 at 16:20
your current query projects only 2 specific columns, which don't include a column named TimeGenerated. my example was just that - an example. you should check the schema of your data aligns with it - if you can, try providing an example using the `datatable operator` to demonstrate how the *input* data/schema look like (After your initial filters/aggregations/joins) - doing that could help adjusting the example for your specific case — Yoni L., Mar 28 '19 at 17:12
Than I guess the sumif won't do what i expect. I need 3 different columns with each column having a different where clausule. no need in creating a column for the TimeGenerated value — achahbar, Apr 01 '19 at 12:20

Azure Application Insights Kusto Language Summurize by where TimeGenerated Value

1 Answers1