Splunk Search

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

verbal_666
Communicator

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 ?

0 Karma
1 Solution

to4kawa
Ultra Champion
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.

View solution in original post

0 Karma

verbal_666
Communicator

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 😉

0 Karma

to4kawa
Ultra Champion
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.

View solution in original post

0 Karma

verbal_666
Communicator

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!!!

0 Karma

to4kawa
Ultra Champion

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

0 Karma

verbal_666
Communicator

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

0 Karma

verbal_666
Communicator

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!