topic Re: How to run two searches and table the results? in Splunk Search

How to run two searches and table the results?

Barty — Wed, 07 Mar 2018 15:11:18 GMT

Good afternoon Guys,

Second question in as many days, but this one is puzzling me and my tiny useless uneducated brain simply cannot work it out.

So, the concept is thus - We ingest an email into our mail index, we then will extract the jobname, start date,start time, end date, end time and then produce a run time. From this, we can then compare the same job run time from the week previous and produce an alert when say the run time has increased by 10%. With me or have I made a right mess of that?!

I have created a HORRIBLE search so far that looks like this:-

index=mail sourcetype=imap cprd 
| rex field=_raw "(?<Jobname>(p-c[a-z\-]+)\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})..([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})\s*([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" max_match=0 
| mvexpand Jobname
| rex field=Jobname "(?<Jobname>(p-c[a-z\-]+))\s*(?<LastRunStartDate>([0-9]{2}\/[0-9]{2}\/[0-9]{4})).(?<LastRunStartTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastRunEndDate>(..([0-9]{2}\/[0-9]{2}\/[0-9]{4}))).(?<LastRunEndTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastStatus>([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))"
| convert timeformat="%T" mktime(LastRunStartTime)  
| fieldformat LastRunStartTime=strftime(LastRunStartTime,"%H:%M:%S")
| convert timeformat="%T" mktime(LastRunEndTime)
| fieldformat LastRunEndTime=strftime(LastRunEndTime,"%T")
| eval RunTime=LastRunEndTime-LastRunStartTime
| rename RunTime as "Run Time (Mins & Secs)"
| table Jobname LastRunStartTime LastRunEndTime "Run Time (Mins & Secs)"

I know, I know, you've probably looked at that and thought - "Oh my, silly boy, could be done so much simpler" and you're right, it can, but I am still very much a junior with regards to splunk searching and methodology. SO, brace yourselves for the next part. What i then want to do is run the same search but for a previous date and compare the run time to list the resulting difference. I have attempted it, and it's horrible:-

index=mail sourcetype=imap cprd latest="03/03/2018:15:00:00"
| rex field=_raw "(?<Jobname>(p-c[a-z\-]+)\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})..([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})\s*([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" max_match=0 
| mvexpand Jobname
| rex field=Jobname "(?<Jobname>(p-c[a-z\-]+))\s*(?<LastRunStartDate>([0-9]{2}\/[0-9]{2}\/[0-9]{4})).(?<LastRunStartTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastRunEndDate>(..([0-9]{2}\/[0-9]{2}\/[0-9]{4}))).(?<LastRunEndTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastStatus>([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))"
| convert timeformat="%T" mktime(LastRunStartTime)  
| fieldformat LastRunStartTime=strftime(LastRunStartTime,"%H:%M:%S")
| convert timeformat="%T" mktime(LastRunEndTime)
| fieldformat LastRunEndTime=strftime(LastRunEndTime,"%T")
| eval RunTime = LastRunEndTime - LastRunStartTime 
| stats list(Jobname) as Job list(RunTime) as Todays_Run
| appendcols
    [search index=mail sourcetype=imap cprd latest="07/03/2018:10:00:00"
| rex field=_raw "(?<Jobname>(p-c[a-z\-]+)\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})..([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})\s*([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" max_match=0 
| mvexpand Jobname
| rex field=Jobname "(?<Jobname>(p-c[a-z\-]+))\s*(?<LastRunStartDate>([0-9]{2}\/[0-9]{2}\/[0-9]{4})).(?<LastRunStartTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastRunEndDate>(..([0-9]{2}\/[0-9]{2}\/[0-9]{4}))).(?<LastRunEndTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastStatus>([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))"
| convert timeformat="%T" mktime(LastRunStartTime)  
| fieldformat LastRunStartTime=strftime(LastRunStartTime,"%H:%M:%S")
| convert timeformat="%T" mktime(LastRunEndTime)
| fieldformat LastRunEndTime=strftime(LastRunEndTime,"%T")
| eval RunTime2 = LastRunEndTime - LastRunStartTime
| stats list(Jobname) as Job list(RunTime2) as Previous_Run]

It's not very happy with this and throwing, understandably, some truncation warnings. Thing is, it kind of works, but now it is listing everything as one statistic again so I cannot run an eval on Todays_run and previous_run. I would reeeeaaallllyy value someone taking a look at this mess and suggesting a much cleaner way to achieve this that my simple mind could comprehend. Thanks guys & gals!

Re: How to run two searches and table the results?

valiquet — Fri, 09 Mar 2018 08:58:09 GMT

Re: How to run two searches and table the results?

Barty — Fri, 09 Mar 2018 09:29:51 GMT

Thank you for the response, I'm a little confused as to what exactly I am asking the timechart to count on though?

Re: How to run two searches and table the results?

deepashri_123 — Fri, 09 Mar 2018 10:55:52 GMT

Hey Barty ,
Can you try something like dis:

index=mail sourcetype=imap cprd latest="03/03/2018:15:00:00" 
| rex field=_raw max_match=0 "(?<Jobname>(p-c[a-z\-]+)\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})..([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})\s*([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" 
| mvexpand Jobname 
| rex field=Jobname "(?<Jobname>(p-c[a-z\-]+))\s*(?<LastRunStartDate>([0-9]{2}\/[0-9]{2}\/[0-9]{4})).(?<LastRunStartTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastRunEndDate>(..([0-9]{2}\/[0-9]{2}\/[0-9]{4}))).(?<LastRunEndTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastStatus>([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" 
| eval LastRunStartTime=strptime(LastRunStartTime,"%H:%M:%S"),LastRunEndTime=strptime(LastRunEndTime,"%H:%M:%S") 
| eval RunTime=LastRunEndTime-LastRunStartTime 
| eval RunTime=strftime(RunTime,"%H:%M:%S") 
| stats values(RunTime) as Previous_Run by jobname 
| join Jobname 
    [ index=mail sourcetype=imap cprd latest="07/03/2018:10:00:00"
    | rex field=_raw max_match=0 "(?<Jobname>(p-c[a-z\-]+)\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})..([0-9]{2}\/[0-9]{2}\/[0-9]{4}).([0-9]{2}:[0-9]{2}:[0-9]{2})\s*([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" 
    | mvexpand Jobname 
    | rex field=Jobname "(?<Jobname>(p-c[a-z\-]+))\s*(?<LastRunStartDate>([0-9]{2}\/[0-9]{2}\/[0-9]{4})).(?<LastRunStartTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastRunEndDate>(..([0-9]{2}\/[0-9]{2}\/[0-9]{4}))).(?<LastRunEndTime>([0-9]{2}:[0-9]{2}:[0-9]{2})|-{5})\s*(?<LastStatus>([SU]{2}|[RU]{2}|[FA]{2}|[IN]{2}))" 
    | eval LastRunStartTime=strptime(LastRunStartTime,"%H:%M:%S"),LastRunEndTime=strptime(LastRunEndTime,"%H:%M:%S") 
    | eval RunTime=LastRunEndTime-LastRunStartTime 
    | eval RunTime=strftime(RunTime,"%H:%M:%S") 
    | stats values(RunTime) as Todays_Run by jobname] 
| table Jobname Previous_Run Todays_Run 
| eval Percentage=round((Todays_Run-Previous_Run/Todays_Run)*100) 
| search Percentage>10

Let me know if this helps!!!

Re: How to run two searches and table the results?

Barty — Fri, 09 Mar 2018 14:02:02 GMT

Good afternoon! I've tried this, and sadly no luck! I remediated the slight issue in as much as there was a missing 'search' after the join, but still no luck in any results.. so close!

Re: How to run two searches and table the results?

valiquet — Fri, 09 Mar 2018 14:16:51 GMT

timewrap will superpose your 2 timecharts representing 2 different time period