topic Re: Working with Lookup in Splunk Search

Working with Lookup

Kwip — Tue, 29 Sep 2020 14:44:40 GMT

I am having below requirements to be merged to create a dashboard/Report.

Need to append my search result to the list of jobs in the look up table. I am having look up table which contains list of jobs. I want to create a table which contains the list of jobs mentioned in my lookup along with its starting and end time. So I created something like this, index=xxx sourcetype=yyy autosysjob=* [mylookup.csv] | stats latest(_time) as ActualEndTime, earliest(_time) as ActualStartTime, latest(Jobstatus) as CurrentStatus by autosysjob | sort by StartTime

The above query resulting in a table like below,
autosysjob -ActualStartTime -ActualEndTime -CurrentStatus
Job1 - 07/01/2017 10:51 - - 07/01/2017 10:55 -SUCCESS
Job2 - 07/01/2017 10:51 - - 07/01/2017 11:20 -RUNNING
Job3 - 07/01/2017 10:51 - - 07/01/2017 10:53 -SUCCESS

I want to have two more field in this table
The time mentioned in the above table is actual start and end time based on the completion. I want have Expected start and end time (which is static value) in the above table. As mentioned below,

autosysjob -ExpectedStartTime -ExpectedEndTime -ActualStartTime -ActualEndTime CurrentStatus
Job1 - 07/01/2017 10:30 - 07/01/2017 10:55 - 07/01/2017 10:40 - - 07/01/2017 10:50 -SUCCESS
Job2 - 07/01/2017 10:50 - 07/01/2017 11:30 - 07/01/2017 10:51 - - 07/01/2017 11:10 -RUNNING
Job3 - 07/01/2017 09:00 - 07/01/2017 10:30 - 07/01/2017 09:00 - - 07/01/2017 10:15 -SUCCESS

My next requirement is Adding one more field based on the Value of CurrentStatus field as mentioned below.

autosysjob -ExpectedStartTime -ExpectedEndTime -ActualStartTime -ActualEndTime CurrentStatus -Action
Job1 - 07/01/2017 10:30 - 07/01/2017 10:55 - 07/01/2017 10:40 - - 07/01/2017 10:50 -SUCCESS - No
Job2 - 07/01/2017 10:50 - 07/01/2017 11:30 - 07/01/2017 10:51 - - 07/01/2017 11:10 -RUNNING - Monitor
Job3 - 07/01/2017 09:00 - 07/01/2017 10:30 - 07/01/2017 09:00 - - 07/01/2017 10:15 -SUCCESS - No
Job4 - 07/01/2017 10:30 - 07/01/2017 10:55 - 07/01/2017 10:40 - - 07/01/2017 10:50 -FAILURE - Critical
Job5 - 07/01/2017 10:50 - 07/01/2017 11:30 - 07/01/2017 10:51 - - 07/01/2017 11:10 -RUNNING - Monitor
Job6 - 07/01/2017 09:00 - 07/01/2017 10:30 - 07/01/2017 09:00 - - 07/01/2017 10:15 -FAILURE - Critical

And the above is my final table. Please help me out to get it done. Thank in advance!

Re: Working with Lookup

adonio — Sun, 02 Jul 2017 01:49:51 GMT

hello @Kwip,
this is a partial answer as i am not 100% clear where in your data are the static values "ExpectedStartTime" and "ExpectedEndTime" ? are they in the lookup? are they tied to each "autosysjob" value or event?
regarding your 2nd question, you can use | eval case function. something like:

... | eval Action = case(CurrentStatus="SUCCESS", "No", CurrentStatus="RUNNING", "Monitor", CurrentStatus="Failure", "Critical")

hope it helps

Re: Working with Lookup

niketn — Sun, 02 Jul 2017 05:57:07 GMT

To add on to @adonio's comment, you need to provide more information for your lookup table, which is not clear from your first search (does not seem to be a working search based on syntax). What are the field names present in mylookup.csv?

You would need to create Lookup definition of your lookup file (either Automatic or Manual based on your needs) using Splunk > Settings > Lookup > Lookup definitions. Following example is assuming you have created manual Lookup Definition with name mylookup (PS: grant proper access permissions otherwise search might not be able to find the lookup definition)

index=xxx sourcetype=yyy [| inputlookup mylookup.csv | table autosysjob] 
| stats latest(_time) as ActualEndTime, earliest(_time) as ActualStartTime, latest(Jobstatus) as CurrentStatus by autosysjob 
| lookup  mylookup autosysjob output ExpectedStartTime, ExpectedEndTime 
| eval Action = case(CurrentStatus="SUCCESS", "No", CurrentStatus="RUNNING", "Monitor", CurrentStatus="Failure", "Critical",true(),"Monitor")
| sort - StartTime
| fieldformat ActualStartTime=strftime(ActualStartTime,"%m/%d/%Y %H:%M")
| fieldformat ActualEndTime=strftime(ActualEndTime,"%m/%d/%Y %H:%M")

Since you are generating ActualStartTime and ActualEndTime from _time field, it will be epoch time and you would need to apply fieldformat to display the same in human readable string time format. ExpectedStartTime and ExpectedEndTime do not need this conversion, since they are coming from lookup they will be string time and hence human readable.
PS: I have added to default true() condition to case statement to map all other Actions as "Monitor"

Re: Working with Lookup

Kwip — Tue, 04 Jul 2017 02:00:39 GMT

Wow!!!! It did the tricks.!!!!!

Thank you @niketnilay and @adonio

@niketnilay - Though my question wasn't clear, your solution hit the bulls-eye. Just addition to your answer, I made lookup as automatic.

Re: Working with Lookup

niketn — Tue, 04 Jul 2017 03:15:44 GMT

@Kwip, glad to hear that we were able to assist. Way to go with Automatic Lookup.

Please Upvote the comments that helped and accept @adonio 's answer to mark this question as answered!

Re: Working with Lookup

Kwip — Tue, 04 Jul 2017 04:06:01 GMT

done!!!!!!!