Splunk Search

Working with Lookup

Kwip
Contributor

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

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

0 Karma
1 Solution

adonio
Ultra Champion

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

View solution in original post

adonio
Ultra Champion

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

niketn
Legend

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"

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Kwip
Contributor

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Kwip
Contributor

done!!!!!!!

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