Longer timeframe for scheduled query rules

%3CLINGO-SUB%20id%3D%22lingo-sub-2674538%22%20slang%3D%22en-US%22%3ELonger%20timeframe%20for%20scheduled%20query%20rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2674538%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20run%20queries%20from%20a%20longer%20time%20period%20(30%20days)%20when%20creating%20a%20scheduled%20query%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AndreiS515_0-1629708562022.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304995i531175236F480BE0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AndreiS515_0-1629708562022.png%22%20alt%3D%22AndreiS515_0-1629708562022.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20thinking%20about%20changing%20the%20%22New%20UserAgent%20observed%20in%20last%2024%20hours%22%20which%20looks%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Elet%20starttime%20%3D%2014d%3B%0Alet%20endtime%20%3D%201d%3B%0Alet%20UserAgentAll%20%3D%0A(union%20isfuzzy%3Dtrue%0A(OfficeActivity%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(starttime)%0A%7C%20where%20isnotempty(UserAgent)%0A%7C%20summarize%20StartTime%20%3D%20min(TimeGenerated)%2C%20EndTime%20%3D%20max(TimeGenerated)%20by%20UserAgent%2C%20SourceIP%20%3D%20ClientIP%2C%20Account%20%3D%20UserId%2C%20Type%2C%20RecordType%2C%20Operation%0A)%2C%0A(%0AW3CIISLog%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(starttime)%0A%7C%20where%20isnotempty(csUserAgent)%0A%7C%20summarize%20StartTime%20%3D%20min(TimeGenerated)%2C%20EndTime%20%3D%20max(TimeGenerated)%20by%20UserAgent%20%3D%20csUserAgent%2C%20SourceIP%20%3D%20cIP%2C%20Account%20%3D%20csUserName%2C%20Type%2C%20sSiteName%2C%20csMethod%2C%20csUriStem%0A)%2C%0A(%0AAWSCloudTrail%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(starttime)%0A%7C%20where%20isnotempty(UserAgent)%0A%7C%20summarize%20StartTime%20%3D%20min(TimeGenerated)%2C%20EndTime%20%3D%20max(TimeGenerated)%20by%20UserAgent%2C%20SourceIP%20%3D%20SourceIpAddress%2C%20Account%20%3D%20UserIdentityUserName%2C%20Type%2C%20EventSource%2C%20EventName%0A))%0A%2F%2F%20remove%20wordSize%20blocks%20of%20non-numeric%20hex%20characters%20prior%20to%20word%20extraction%0A%7C%20extend%20UserAgentNoHexAlphas%20%3D%20replace(%22(%5BA-Fa-f%5D%7B4%2C%7D)%22%2C%20%22x%22%2C%20UserAgent)%0A%2F%2F%20once%20blocks%20of%20hex%20chars%20are%20removed%2C%20extract%20wordSize%20blocks%20of%20a-z%0A%7C%20extend%20Tokens%20%3D%20extract_all(%22(%5BA-Za-z%5D%7B4%2C%7D)%22%2C%20UserAgentNoHexAlphas)%0A%2F%2F%20concatenate%20extracted%20words%20to%20create%20a%20summarized%20user%20agent%20for%20baseline%20and%20comparison%0A%7C%20extend%20NormalizedUserAgent%20%3D%20strcat_array(Tokens%2C%20%22%7C%22)%0A%7C%20project-away%20UserAgentNoHexAlphas%2C%20Tokens%3B%0AUserAgentAll%0A%7C%20where%20StartTime%20%26gt%3B%3D%20ago(endtime)%0A%7C%20summarize%20StartTime%20%3D%20min(StartTime)%2C%20EndTime%20%3D%20max(EndTime)%2C%20count()%20by%20UserAgent%2C%20NormalizedUserAgent%2C%20SourceIP%2C%20Account%2C%20Type%2C%20RecordType%2C%20Operation%2C%20EventSource%2C%20EventName%2C%20sSiteName%2C%20csMethod%2C%20csUriStem%0A%7C%20join%20kind%3Dleftanti%0A(%0AUserAgentAll%0A%7C%20where%20StartTime%20%26lt%3B%20ago(endtime)%0A%7C%20summarize%20by%20NormalizedUserAgent%2C%20SourceIP%2C%20Account%2C%20Type%2C%20RecordType%2C%20Operation%2C%20EventSource%2C%20EventName%2C%20sSiteName%2C%20csMethod%2C%20csUriStem%0A)%0Aon%20NormalizedUserAgent%0A%7C%20extend%20timestamp%20%3D%20StartTime%2C%20IPCustomEntity%20%3D%20SourceIP%2C%20AccountCustomEntity%20%3D%20Account%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20increase%20the%20starttime%20to%2030d%20to%20check%20if%20the%20new%20user%20agents%20haven't%20appeared%20in%20the%20last%2030%20days%20instead%20of%2014%20days.%20By%20default%20it%20looks%20up%20data%20from%20the%20past%2014%20days%2C%20how%20would%20this%20affect%20my%20new%20starttime%20of%2030d%2C%20does%20it%20still%20look%20up%20data%20from%20the%20past%2014d%20or%2030d%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Is it possible to run queries from a longer time period (30 days) when creating a scheduled query?

AndreiS515_0-1629708562022.png

 

I'm thinking about changing the "New UserAgent observed in last 24 hours" which looks something like this:

 

 

let starttime = 14d;
let endtime = 1d;
let UserAgentAll =
(union isfuzzy=true
(OfficeActivity
| where TimeGenerated >= ago(starttime)
| where isnotempty(UserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = ClientIP, Account = UserId, Type, RecordType, Operation
),
(
W3CIISLog
| where TimeGenerated >= ago(starttime)
| where isnotempty(csUserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent = csUserAgent, SourceIP = cIP, Account = csUserName, Type, sSiteName, csMethod, csUriStem
),
(
AWSCloudTrail
| where TimeGenerated >= ago(starttime)
| where isnotempty(UserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = SourceIpAddress, Account = UserIdentityUserName, Type, EventSource, EventName
))
// remove wordSize blocks of non-numeric hex characters prior to word extraction
| extend UserAgentNoHexAlphas = replace("([A-Fa-f]{4,})", "x", UserAgent)
// once blocks of hex chars are removed, extract wordSize blocks of a-z
| extend Tokens = extract_all("([A-Za-z]{4,})", UserAgentNoHexAlphas)
// concatenate extracted words to create a summarized user agent for baseline and comparison
| extend NormalizedUserAgent = strcat_array(Tokens, "|")
| project-away UserAgentNoHexAlphas, Tokens;
UserAgentAll
| where StartTime >= ago(endtime)
| summarize StartTime = min(StartTime), EndTime = max(EndTime), count() by UserAgent, NormalizedUserAgent, SourceIP, Account, Type, RecordType, Operation, EventSource, EventName, sSiteName, csMethod, csUriStem
| join kind=leftanti
(
UserAgentAll
| where StartTime < ago(endtime)
| summarize by NormalizedUserAgent, SourceIP, Account, Type, RecordType, Operation, EventSource, EventName, sSiteName, csMethod, csUriStem
)
on NormalizedUserAgent
| extend timestamp = StartTime, IPCustomEntity = SourceIP, AccountCustomEntity = Account

 

and increase the starttime to 30d to check if the new user agents haven't appeared in the last 30 days instead of 14 days. By default it looks up data from the past 14 days, how would this affect my new starttime of 30d, does it still look up data from the past 14d or 30d?

0 Replies