Forum Discussion

Nikhil_Babu_Battula's avatar
Nikhil_Babu_Battula
Copper Contributor
Oct 09, 2022

Query to compare values from last week to values generated today

I want to compare the total `TriggersStarted` (from MetricName column) from last week to `TriggersStarted` (from MetricName column) today

For that I am using the following query

 

AzureMetrics | where TimeGenerated > ago(7d)
| where MetricName == "TriggersStarted"
| summarize count()

 

With this query, I got total count `TriggersStarted` from last 7 days but I got stuck at assigning that value to variable using `let`. And can I assign the result (from query) to a custom column using extend operator? And to compare to present day `TriggersStarted`.

2 Replies

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Oct 10, 2022

    Nikhil_Babu_Battula 

    How about?

    AzureMetrics 
| where TimeGenerated between ( ago(7d) .. endofday(ago(1d)) )
| where MetricName == "TriggersStarted"
| summarize LastWeek=count() by MetricName
| join 
(
    AzureMetrics 
    | where TimeGenerated between (startofday(ago(0d)) .. now() )
    | where MetricName == "TriggersStarted"
    | summarize TodaysoFar=count()by MetricName
) on MetricName
| project-away MetricName1
    • Nikhil_Babu_Battula's avatar
      Nikhil_Babu_Battula
      Copper Contributor
      Oct 11, 2022

      Thanks for the help Clive_Watson i am trying to calculate number of triggers triggerstarted by hour in last week (7*24 = 168 hours). And out of those 168 hour bucket triggers .i want to find the hour which has min number of triggersStarted. And compare that to this week current hour triggers for that i made small changes to your query. if u dont mind could u pls guide which functions to use? to get that  query? for that if i tried using bin it just rounding off to the offset value, pivot, range functions but didnt got expected results