Equivalent of timeshift operator in Azure Log Analytics (KQL)

Question

Hi,&nbsp; &nbsp;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 &gt;0 indicating the missing ones .&nbsp;Thanks

clivewatson · Answer

debashish2021&nbsp;
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 &gt; startofday(now())
        | where DataType == "Alert"
        // get the last record from today 
        | summarize TodaysCount = count(), arg_max(TimeGenerated,*) by DataType
) on DataType
result

you can then use something like:| where TodaysCount &gt; 7daysAgo&nbsp;

debashish2021 · Answer

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

clivewatson · Answer

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).

Forum Discussion

Equivalent of timeshift operator in Azure Log Analytics (KQL)

3 Replies

Resources