Day/week/Time based Analytical (scheduled) rule in Azure Sentinel

%3CLINGO-SUB%20id%3D%22lingo-sub-1866317%22%20slang%3D%22en-US%22%3EDay%2Fweek%2FTime%20based%20Analytical%20(scheduled)%20rule%20in%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1866317%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20working%20with%20a%20client%20on%20a%20certain%20requirement%20for%20detection%20of%20an%20office%20365%20message%20activity%20based%20on%20%3CU%3E%3CSTRONG%3Etime%20and%20date%3C%2FSTRONG%3E%3C%2FU%3E.%20below%20business%20use%20case%20in%20detail%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EUse%20case%3A%3C%2FSTRONG%3E%20emails%20sent%20to%20external%20domain%3C%2FP%3E%3COL%3E%3CLI%3Eafter%20office%20hours%20in%20working%20days%20(i.e.%20between%205%3A00%20PM%20to%209%3A00%20AM)%20and%3C%2FLI%3E%3CLI%3Esame%20activity%20any%20time%20on%20non-working%20days%20(i.e.%20any%20time%20on%20Saturdays%20and%20Sundays)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20first%20use%20case%2C%20I%20have%20created%20KQL%20for%20detection%20based%20on%20Time%20hours%20and%20%3CSTRONG%3Eit%20is%20working%20%3C%2FSTRONG%3Ebut%26nbsp%3B%20the%20rule%20runs%20only%20on%20certain%20time%20during%20the%20weekend%20because%20I%20need%20the%20rule%20to%20detect%20activities%20anytime%20on%20weekend%2C%20below%20query%20for%20reference%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3EO%3C%2FSPAN%3E%3CSPAN%3E365%3C%2FSPAN%3E%3CSPAN%3EMessageTrace_CL%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BRecipientAddress_s%26nbsp%3B!%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22m365x971587.onmicrosoft.com%22%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BReceivedTimeStamp%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(format_%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(Received_t%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22HH%22%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BReceivedTimeStamp%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%2217%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2218%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2219%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2220%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2221%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2222%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2223%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2200%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2201%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2202%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2203%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2204%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2205%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2206%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2207%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%2208%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BReceived_t%2C%26nbsp%3BReceivedTimeStamp%2C%26nbsp%3BSenderAddress_s%2C%26nbsp%3BRecipientAddress_s%2C%26nbsp%3BSubject_s%2C%26nbsp%3BStatus_s%2C%26nbsp%3BMessageTraceId_g%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%222%22%20color%3D%22%230000FF%22%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BAccountCustomEntity%26nbsp%3B%3D%26nbsp%3BSenderAddress_s%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSTRONG%3ERequire%20Help%20on%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3Erunning%20the%20first%20rule%20only%20on%20weekdays%20(M%2CT%2CW%2CT%2CF)%20so%20that%20it%20detects%20activity%20between%20certain%20timeframe%20as%20mentioned%20in%20KQL.%3C%2FLI%3E%3CLI%3Escheduling%20the%20second%20rule%20to%20run%20only%20on%20weekend%20irrespective%20of%20timeframe%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EP.S%3A%3C%2FSTRONG%3E%20i%20used%20logic%20app's%20recurrence%20based%20on%20date%20and%20time%20but%20logic%20app%20doesn't%20support%3C%2FP%3E%3CP%3Eenabling%2Fdisabling%20a%20sentinel%20rule%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22pic.PNG%22%20style%3D%22width%3A%20382px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232296iB2470DFCEC08F369%2Fimage-dimensions%2F382x168%3Fv%3D1.0%22%20width%3D%22382%22%20height%3D%22168%22%20role%3D%22button%22%20title%3D%22pic.PNG%22%20alt%3D%22pic.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1872160%22%20slang%3D%22en-US%22%3ERe%3A%20Day%2Fweek%2FTime%20based%20Analytical%20(scheduled)%20rule%20in%20Azure%20Sentinel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1872160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660803%22%20target%3D%22_blank%22%3E%40KrishhnaM%3C%2FA%3E%26nbsp%3BYou%20could%20use%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fdayofweekfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Edayofweek%3C%2FA%3E%26nbsp%3Bfunction%20to%20determine%20if%20it%20is%20a%20weekend%20or%20not%20and%20then%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fiiffunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eiif%3C%2FA%3E%26nbsp%3Bstatement%20to%20handle%20different%20hour%20of%20the%20day%20processing.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Hi Community,

 

I am currently working with a client on a certain requirement for detection of an office 365 message activity based on time and date. below business use case in detail

 

Use case: emails sent to external domain

  1. after office hours in working days (i.e. between 5:00 PM to 9:00 AM) and
  2. same activity any time on non-working days (i.e. any time on Saturdays and Sundays)

 

For the first use case, I have created KQL for detection based on Time hours and it is working but  the rule runs only on certain time during the weekend because I need the rule to detect activities anytime on weekend, below query for reference

 

O365MessageTrace_CL
where RecipientAddress_s !contains "m365x971587.onmicrosoft.com"
extend ReceivedTimeStamp = tostring(format_datetime(Received_t, "HH"))
where ReceivedTimeStamp in ("17""18""19""20""21""22""23""00""01""02""03""04""05""06""07""08")
project Received_t, ReceivedTimeStamp, SenderAddress_s, RecipientAddress_s, Subject_s, Status_s, MessageTraceId_g
extend AccountCustomEntity = SenderAddress_s
 

Require Help on:

  1. running the first rule only on weekdays (M,T,W,T,F) so that it detects activity between certain timeframe as mentioned in KQL.
  2. scheduling the second rule to run only on weekend irrespective of timeframe

 

P.S: i used logic app's recurrence based on date and time but logic app doesn't support

enabling/disabling a sentinel rule
pic.PNG

2 Replies

@KrishhnaM You could use a dayofweek function to determine if it is a weekend or not and then and iif statement to handle different hour of the day processing.