Splunk Search

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

verbal_666
Builder

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
Builder

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.

0 Karma

verbal_666
Builder

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
Builder

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

0 Karma

verbal_666
Builder

... 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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...