Equivalent of timeshift operator in Azure Log Analytics (KQL)

%3CLINGO-SUB%20id%3D%22lingo-sub-2493552%22%20slang%3D%22en-US%22%3EEquivalent%20of%20timeshift%20operator%20in%20Azure%20Log%20Analytics%20(KQL)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493552%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI%20am%20looking%20for%20something%20equivalent%20to%20a%20timeshift%20operator%20.%20For%20example%20a%20query%20returns%20x%20results%20when%20run%20in%20the%20last%2015%20minutes%20%2C%20but%20the%20same%20query%20returns%20y%20results%20when%20run%20exactly%20a%20week%20back%20i.e.%20currenttime%20-7%20days%20(%20also%20run%20for%2015%20minutes%20a%20week%20back)%20.%3C%2FP%3E%3CP%3EMy%20purpose%20is%20to%20get%20the%20differential%20between%20these%20values%20(%20y-x)%20and%20alert%20if%20this%20number%20is%20%26gt%3B0%20indicating%20the%20missing%20ones%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2495925%22%20slang%3D%22en-US%22%3ERe%3A%20Equivalent%20of%20timeshift%20operator%20in%20Azure%20Log%20Analytics%20(KQL)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2495925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088447%22%20target%3D%22_blank%22%3E%40debashish2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20example%20will%20give%20you%20the%20structure.%20I%20used%20the%20Usage%20table%20as%20an%20example%20and%20the%20Alerts%20table%20(which%20you%20may%20or%20may%20not%20have)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EUsage%0A%2F%2F%20just%20data%20from%207%20days%20ago%20(midnight%20to%20midnight)%0A%7C%20where%20TimeGenerated%20between%20(%20startofday(ago(7d))%20..%20endofday(ago(7d))%20)%0A%7C%20where%20DataType%20%3D%3D%20%22Alert%22%0A%7C%20summarize%207daysAgo%20%3D%20count()%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%20by%20DataType%0A%7C%20join%20(%0A%20%20%20%20Usage%0A%20%20%20%20%2F%2F%20just%20data%20from%20midnight%20TODAY%20until%20now%20%0A%20%20%20%20%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(now())%0A%20%20%20%20%20%20%20%20%7C%20where%20DataType%20%3D%3D%20%22Alert%22%0A%20%20%20%20%20%20%20%20%2F%2F%20get%20the%20last%20record%20from%20today%20%0A%20%20%20%20%20%20%20%20%7C%20summarize%20TodaysCount%20%3D%20count()%2C%20arg_max(TimeGenerated%2C*)%20by%20DataType%0A)%20on%20DataType%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eresult%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-29%20091813.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292243iF0E3488D76B55CD9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-29%20091813.png%22%20alt%3D%22Screenshot%202021-06-29%20091813.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20can%20then%20use%20something%20like%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20where%20TodaysCount%20%26gt%3B%207daysAgo%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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).