topic Re: Average time between two jobs. in Splunk Search

Average time between two jobs.

carlyleadmin — Tue, 29 Sep 2020 15:53:16 GMT

Hi,

Here is my search query;

index=* sourcetype="WMI:WinEventLog:Application" SourceName="Investran RS Word Processing Service" Message=* | table Message , SourceName _time |dedup _time |sort -_time

and this brings up ;

So what i am trying to do if possible is,calculate the average time between stop/start.and if that average is greater than lets say 10 mins only bring that results/messages

Thanks,

Re: Average time between two jobs.

Sukisen1981 — Tue, 29 Sep 2020 15:49:04 GMT

Try this -

Re: Average time between two jobs.

lfedak_splunk — Wed, 20 Sep 2017 19:19:51 GMT

Hey @carlyleadmin, If @Sukisen1981's solution worked then please don't forget to accept their answer to award karma points and close the question. 🙂

Re: Average time between two jobs.

carlyleadmin — Wed, 20 Sep 2017 19:23:28 GMT

Thanks for the quick reply sukisen1981.i will try it and let you know.even if it doesn't work i will accept it and give you points:)but i am hoping that we can keep the case open if possible

Re: Average time between two jobs.

carlyleadmin — Wed, 20 Sep 2017 19:24:09 GMT

i hope we can keep the case open for couple days untill i give this a try.

Thanks

Re: Average time between two jobs.

Sukisen1981 — Thu, 21 Sep 2017 05:29:29 GMT

Hi,

The intent here is not to get points , but to make things work....This is a community where people ask / receive help, please feel free to revert back if the query does not work or you have difficulties in executing / understanding the query 🙂

Regards,
Suki

Re: Average time between two jobs.

carlyleadmin — Thu, 21 Sep 2017 11:20:03 GMT

well i have to disagree with you suki.points are everything:)yes i am new to the splunk and there are so many functions to learn and your query is bit complicated for someone like me,and it takes time for me to learn it.i don't want to just copy paste the query,i wanna learn it as well.

your query works in a way,but doesn't do what i really want it to do.if you look at the attached screenshot,i want the query to return the highligted line/lines in my data.where the service stopped on 2017-09-13 13:57:49 and started back on 2017-09-15 14:25:47. as you can see the gap between 2 services are greater than 10 mins.your search returns mostly "service started" results and there are couple "service stopped" ones as well,but that does not help me.i need that correlation.stop-start time>10-15 mins.i hope this is clear,but if you need more time think about it and don't understand the question, it is okay,take your time:)

the only reason i asked the case to be kept open so i could tweak your search query and maybe make it work the way i wanted.your query does not work completely and as you mentioned, this is a community where people ask/receive help(points) i shall take your point back:)

Thanks!!!!

Re: Average time between two jobs.

carlyleadmin — Thu, 21 Sep 2017 11:24:10 GMT

i forgot to add the screenshot

Re: Average time between two jobs.

Sukisen1981 — Thu, 21 Sep 2017 11:24:25 GMT

can you please reattach the screen shot?

Re: Average time between two jobs.

Sukisen1981 — Tue, 29 Sep 2020 15:54:06 GMT

the streamstats is pulling the previous time as prevt1, now you can add a , after prevt1 and add something like -
streamstats current=false last(t1) as prevt1 , current=false last(Mesage) as prevmsg..this will fetch the previous message and the previous time
now , in the eval :
eval diff=round((prevt1-t1)*60/3600,2)| where diff >10 AND prevmsg ="Service started successfully" AND Message="Service stopped successfully"... this will give you ONLY rows having service stop AND previous row was service start AND diff between the time stamps is >10..try it out no reason it won't work.

I am not going into the whole points debate, it is not worth it 🙂 🙂 but trying out and tweaking the query is definitely worth it, which you already seem eager to explore...Happy Splunking 🙂

Re: Average time between two jobs.

carlyleadmin — Thu, 21 Sep 2017 11:46:15 GMT

and this is what i get when i run your query.mostly just "service started" results

thanks.![alt text][2]

![![alt text][2]][1] [2]: /storage/temp/217606-start-search.png

Re: Average time between two jobs.

carlyleadmin — Thu, 21 Sep 2017 11:47:42 GMT

Thanks Suki.all is good.and like i said, i am not as experienced as you guys and that's why i am here:)i just started using splunk couple weeks ago and i am amazed what it can do.such a powerful tool.Thanks for all the help.

Happy Splunking:)

Re: Average time between two jobs.

carlyleadmin — Tue, 29 Sep 2020 16:00:57 GMT

Hey Sukisen,

this is what i am running but not getting anything."No result found"

source="WinEventLog:Application" host=xxxx SourceName="Investran RS Word Processing Service" Message=*|eval t=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval t1=strptime(t,"%Y-%m-%d %H:%M:%S")
| streamstats current=false last(t1) as prevt1, last(Message) as prevmsg|eval diff=round((prevt1-t1)*60/3600,2)| where diff>10 AND prevmsg="Service started successfully" AND Message="Service stopped successfully"| table Message,SourceName,_time

Re: Average time between two jobs.

carlyleadmin — Tue, 03 Oct 2017 20:18:48 GMT

Can you help please?

Re: Average time between two jobs.

Sukisen1981 — Tue, 03 Oct 2017 20:29:56 GMT

can you please try removing the pipes starting one by one before the first eval and let me know after which pipe the search first returns no results?

Re: Average time between two jobs.

carlyleadmin — Tue, 29 Sep 2020 16:01:00 GMT

i get results up to this point;
source="WinEventLog:Application" host=HC1APTR2CV SourceName="Investran RS Word Processing Service" Message=*|eval t=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval t1=strptime(t,"%Y-%m-%d %H:%M:%S")
| streamstats current=false last(t1) as prevt1, last(Message) as prevmsg|eval diff=round((prevt1-t1)*60/3600,2)

after that it fails.is that what you asked for?
thanks

Re: Average time between two jobs.

carlyleadmin — Tue, 03 Oct 2017 20:40:31 GMT

so if i add:

| where diff >10 AND prevmsg ="Service started successfully" AND Message="Service stopped successfully"

i dont get any results

Re: Average time between two jobs.

Sukisen1981 — Tue, 03 Oct 2017 20:45:41 GMT

yes ..cool can you remove the |where....
nd just modify |table... to
table Message,SourceName,_time,diff,prevmsg..?
I need the output..is diff n prevmesg values returned in the table?
plz attacha screen shot of the output from the statistics table if possible

Re: Average time between two jobs.

carlyleadmin — Wed, 04 Oct 2017 12:53:56 GMT

Re: Average time between two jobs.

carlyleadmin — Wed, 04 Oct 2017 12:58:52 GMT

i added the screenshot at the bottom of the page.i still need to be able to get just the messages where time between stop services and start services is more than 10 minutes.i'd like to omit the results where there is just start services coming as well.

i want to create an alert when this service doesn't start in 10 minutes so service desk would get an email and manually start the service.

thanks,