Splunk Search

Average time between two jobs.

carlyleadmin
Contributor

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 ;

alt text

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,

Tags (1)
0 Karma
1 Solution

carlyleadmin
Contributor

alt text

i forgot to add the screenshot

View solution in original post

0 Karma

carlyleadmin
Contributor

alt text

0 Karma

carlyleadmin
Contributor

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:)

0 Karma

carlyleadmin
Contributor

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

0 Karma

carlyleadmin
Contributor

alt text

i forgot to add the screenshot

0 Karma

Sukisen1981
Champion

well this shouldn't take too much time.
look at my query:
|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
| eval diff=round((prevt1-t1)*60/3600,2)| where diff >10
| table Message,Soucename,_time

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 🙂

0 Karma

carlyleadmin
Contributor

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

0 Karma

carlyleadmin
Contributor

Can you help please?

0 Karma

Sukisen1981
Champion

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?

0 Karma

carlyleadmin
Contributor

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

0 Karma

Sukisen1981
Champion

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

0 Karma

carlyleadmin
Contributor

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,

0 Karma

carlyleadmin
Contributor

so if i add:

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

i dont get any results

0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

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

0 Karma

carlyleadmin
Contributor

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

Thanks

0 Karma

Sukisen1981
Champion

Try this -

|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
| eval diff=round((prevt1-t1)*60/3600,2)| where diff >10
| table Message,Soucename,_time

0 Karma

carlyleadmin
Contributor

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

0 Karma

Sukisen1981
Champion

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

0 Karma

carlyleadmin
Contributor

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

Thanks!!!!

0 Karma

Sukisen1981
Champion

can you please reattach the screen shot?

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...