I am having below requirements to be merged to create a dashboard/Report.
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!
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
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
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"
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.
@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!
done!!!!!!!