topic Re: SPL query to check event START... and END (if there is!!??!!) in Splunk Search

SPL query to check event START... and END (if there is!!??!!)

verbal_666 — Wed, 30 Sep 2020 04:53:32 GMT

I guys.
Recently i came in trouble to resolve the "puzzle" described in Title...

What we need
1) Trigger the "Job_Start", always
2) Monitor its processation

Variables
1) "Job_Start" is dynamic, i can have it at 01:00 so at 04:30, 15:00 or 17:15 (and so on....h24): so "Job_Start" is the beginning point!!!
2) "Job_End" is the great variable: it could exists, as NOT AT ALL, and the focal point is to check if IT EXISTS in a range time of max 2h from "Job_Start"

What i originally did,

tag=mytag host=server earliest=-3h
|transaction maxspan=120m maxevents=-1 startswith="Job_Start" endswith="Job_End" host,source
|[...........do all if statements by "duration" field]

... ok, but what if Job never ends???

tag=mytag host=server earliest=-3h
|transaction maxspan=120m maxevents=-1 startswith="Job_Start" host,source
|eval CHECK_END=if(match(_raw,"Job_End"),_time,"X")
|[...........do all if statements by "duration" field plus "CHECK_END" variable]

... ok, this is a good compromise to work...

Now, what i really scheduled (every 15 minutes), after thinking of possible missing timings or other things...

tag=mytag host=server earliest=-3h|sort + _time|eventstats first(_time) as tSTART last(_time) as tEND|eval RANGE=round((tEND-tSTART)/60)
|eval CHECK_START=if(match(_raw,"Job_Start"),_time,"X")
|eval CHECK_END=if(match(_raw,"Job_End"),_time,"X")
|stats min(CHECK_START) as START min(CHECK_END) as END last(RANGE) as RANGE
|where START!="X"

|eval DUR=round((END-START)/60)|eval PASS=round((now()-START)/60)

|eval msg=if( (START="X") AND (END="X"),"NO Job_Start last "+RANGE,msg)|eval nota="already skipped with where above!"
|eval msg=if( (START!="X") AND (END="X") AND (PASS>120),"Job_Start no Job_End after "+PASS,msg)
|eval msg=if( (START!="X") AND (END!="X") AND (PASS>120),"Job_Start with Job_End after "+DUR,msg)

|eval host="server"| eval source="mylog"
|eval displaythis="LOG:"+source+"__"+msg+"__[test]" | eval TimeStamp=strftime(now(),"%Y%m%d.%H%M%S") | table TimeStamp host displaythis

... the schedule is running... still have to test its real effects...

Now, some advice or help about what did above, and WHAT COULD BE DONE BETTER AND MORE EFFICIENTLY ?

Re: SPL query to check event START... and END (if there is!!??!!)

verbal_666 — Tue, 07 Apr 2020 18:57:40 GMT

... maybe there's already a little "bug", better so,

|eval msg=if( (START!="X") AND (END!="X") AND (DUR>120),"Job_Start with Job_End after "+DUR,msg)

... anyway, waiting if the "process" is correct or there's one more efficent.
Thanks.

Re: SPL query to check event START... and END (if there is!!??!!)

to4kawa — Wed, 30 Sep 2020 04:58:43 GMT

tag=mytag host=server earliest=-3h 
| reverse
| streamstats count(eval(searchmatch("Job_Start"))) as session by host source
| stats range(eval(if(searchmatch("Job_Start") OR searchmatch("Job_End"),_time,NULL))) as duration by session host source

Try this and make eval function.
This query makes duration from Job_Start to Job_End (if exist) by each host and source.

note: duration is sec.

Re: SPL query to check event START... and END (if there is!!??!!)

verbal_666 — Thu, 09 Apr 2020 08:08:50 GMT

Wow, very very interesting. The original "workaround" is running, i did some minimal change,

tag=mytag host=server earliest=-3h|sort + _time|eventstats first(_time) as tSTART last(_time) as tEND|eval RANGE=round((tEND-tSTART)/60)
|eval CHECK_START=if(match(_raw,"Job_Start"),_time,"X")
|eval CHECK_END=if(match(_raw,"Job_End"),_time,"X")
|stats min(CHECK_START) as START min(CHECK_END) as END last(RANGE) as RANGE
|where START!="X"

|eval DUR=round((END-START)/60)|eval PASS=round((now()-START)/60)

|eval msg=""
|eval msg=if( (START="X") AND (END="X"),"NO FileDiscoveryJob:95 last "+RANGE,msg)|eval nota="already skipped with where above!"
|eval msg=if( (START!="X") AND (END="X") AND (PASS>120),"FileDiscoveryJob:95 no AcquisitionAction:264 after "+PASS,msg)
|eval msg=if( (START!="X") AND (END!="X") AND (DUR>120),"FileDiscoveryJob:95 with AcquisitionAction:264 after "+DUR,msg)
|where msg!=""

|eval host="server"| eval source="mylog"
|eval displaythis="LOG:"+source+"__"+msg+"__[test]" | eval TimeStamp=strftime(now(),"%Y%m%d.%H%M%S") | table TimeStamp host displaythis

But you SPL is extremely efficient and advanced!!!
Many thanks, i'll test asap, and maybe do the right correlations with this.
Thnaks a lot, very kind!!!

Re: SPL query to check event START... and END (if there is!!??!!)

to4kawa — Thu, 09 Apr 2020 08:15:18 GMT

I answered a lot, but this is the first time that I have been pleased like you.
Thank you very much　@verbal_666

Re: SPL query to check event START... and END (if there is!!??!!)

verbal_666 — Thu, 09 Apr 2020 08:34:31 GMT

You're welcome man... thanks again 😉
(very smart solution 🙂 )

Re: SPL query to check event START... and END (if there is!!??!!)

verbal_666 — Thu, 09 Apr 2020 10:11:16 GMT

After running some tests,
i made this schedule running every 30m.
Should, for now, make the "trick", next i monitor the process and (tranks to @to4kawa) plan to optimize with better SPL...

tag=mytag host=server earliest=-3h|sort + _time|eventstats first(_time) as tSTART last(_time) as tEND|eval RANGE=round((tEND-tSTART)/60)
|eval CHECK_START=if(match(_raw,"Job_Start"),_time,0)
|eval CHECK_END=if(match(_raw,"Job_End"),_time,0)
|stats max(CHECK_START) as START max(CHECK_END) as END last(RANGE) as RANGE
|where START!=0

|eval DUR=round((END-START)/60)|eval PASS=round((now()-START)/60)

|eval msg=""
|eval msg=if( (START=0) AND (END=0),"INFO - NO Job_Start last "+RANGE,msg)|eval nota="already skipped with where above!"
|eval msg=if( (START!=0) AND (END=0) AND (PASS>120),"KO - Job_Start no Job_End after "+PASS,msg)
|eval msg=if( (START!=0) AND (END!=0) AND (DUR>120),"KO - Job_Start with Job_End after "+DUR,msg)
|eval msg=if( (START!=0) AND (END!=0) AND (DUR<=120),"OK - Job_Start with Job_End after "+DUR,msg)
|where msg!=""

|eval host="server"| eval source="mylog"
|eval displaythis="LOG:"+source+"__"+msg+"__[test]" | eval TimeStamp=strftime(now(),"%Y%m%d.%H%M%S") | table TimeStamp host displaythis

Surely i'm getting out of Best Practices for SPL... but if works, let's do it 🙂
SPL is great... but could become very complex 😉