Equivalent of timeshift operator in Azure Log Analytics (KQL)

Copper Contributor

Hi,

   I am looking for something equivalent to a timeshift operator . For example a query returns x results when run in the last 15 minutes , but the same query returns y results when run exactly a week back i.e. currenttime -7 days ( also run for 15 minutes a week back) .

My purpose is to get the differential between these values ( y-x) and alert if this number is >0 indicating the missing ones .

 

Thanks

3 Replies

@debashish2021 

This example will give you the structure. I used the Usage table as an example and the Alerts table (which you may or may not have)

Usage
// just data from 7 days ago (midnight to midnight)
| where TimeGenerated between ( startofday(ago(7d)) .. endofday(ago(7d)) )
| where DataType == "Alert"
| summarize 7daysAgo = count(), min(TimeGenerated), max(TimeGenerated) by DataType
| join (
    Usage
    // just data from midnight TODAY until now 
        | where TimeGenerated > startofday(now())
        | where DataType == "Alert"
        // get the last record from today 
        | summarize TodaysCount = count(), arg_max(TimeGenerated,*) by DataType
) on DataType

result

Screenshot 2021-06-29 091813.png

you can then use something like:

| where TodaysCount > 7daysAgo 

Thanks ! This is good but how will it work when we try to set up alert using the Log Alert .
This will work fine when I run it in Log Analytics using 'Set In Query' option for timeRange. But when I try to set up an alert with this it has to have the period which is a multiple of minutes ( max upto equivalent of 2 days) . When I set that the Timegenerated section of the query is overridden by the one selected in Period and expected results are not returned . Is there a way to get around this ?

Thanks
Sorry I wasn't aware this was for an Alert, I don't think Alerts support this as you say (but I'm no expert on Alerts).