host=aa* | search env=CERT (job=AJOB OR job= BJOB OR job= CJOB ) | eval desired_time=strftime(_time, "%d/%m/%Y %I:%M:%S %p") | table job msg | sort desired_time | rename job as "Job Name"| rename desired_time as "Completion-Time"
If data not available is there a way to get below output.
Job Name Completion-Time Last-Completion-Time Delay(Completion-Time-Last-Completion-Time)
AJOB Not Yet completed 02-13-2017 10mins
BJOB Not Yet completed 02-13-2017 10mins
CJOB Not Yet completed 02-13-2017 10mins
This generates some test data from your samples
| makeresults | eval mydata="2/12/17 8:12:40.866 PM 20170213/203040.866 - U0011502 Workflow 'AJob' (RunID '12111') ended normally.
host = host2 source = /xxx/uc4/AutomationEngine/temp/abc.txt sourcetype = uc4
!!!!2/12/17 6:12:06.744 PM 20170213/182906.744 - U0011502 Workflow 'BJob' (RunID '12122') ended normally.
host = host1 source = /M/zx/AutomationEngine/temp/def1.txt sourcetype = uc4
!!!!2/14/17 8:30:40.866 PM 20170213/203040.866 - U0011502 Workflow 'AJob' (RunID '14111') ended normally.
host = host2 source = /xxx/uc4/AutomationEngine/temp/abc.txt sourcetype = uc4
!!!!2/14/17 6:29:06.744 PM 20170213/182906.744 - U0011502 Workflow 'BJob' (RunID '14122') ended normally.
host = host1 source = /M/zx/AutomationEngine/temp/def1.txt sourcetype = uc4
!!!!2/15/17 8:14:40.866 PM 20170213/203040.866 - U0011502 Workflow 'AJob' (RunID '15111') ended normally.
host = host2 source = /xxx/uc4/AutomationEngine/temp/abc.txt sourcetype = uc4"
| makemv delim="!!!!" mydata
| mvexpand mydata
| eval _raw = mydata
| rex field=_raw "^(?<TheTime>\d+\/\d+\/\d+\s\d+:\d+:\d+\.\d+\s\w+)"
| eval _time = strptime(TheTime,"%m/%d/%y %l:%M:%S.%3N %p")
| table _time _raw
This calculates a report. You probably will have to tweak the format of the "delay" variable" to your liking.
| rex field=_raw "host = (?<host>[^\s]+)\s+source = (?<source>[^\s]+)\s+sourcetype = (?<sourcetype>[^\s]+)"
| rex field=_raw "Workflow \'(?<job>[^\']+)\' \(RunID \'(?<runid>[^\']+)\'\) ended normally"
| stats max(_time) as lasttime by job
| eval today=relative_time(now(),"-5h@d")
| eval delay = now()-lasttime
| eval delay=if(delay<86400,"",round((delay % 86400)/1440,2)." hours")
| eval status=if(lasttime<today,"not yet complete", "complete")
| eval desired_time=if(status="complete","",strftime(lasttime,"%m-%d-%Y %H:%M:%S %p"))
| eval completed_time=if(status="complete",strftime(lasttime,"%m-%d-%Y %H:%M:%S %p"),"")
| table job status TheTime completed_time desired_time delay
If you wanted to limit the report to only certain jobs, you could either do that in your initial search, or put a join or a lookup table right after the stats command.
These commands in order do not make sense. Renaming the same field twice, with no lookup or join involved in between, is a waste of a command. Also, as two commenters posted, the table command strips all but the listed fields, so there aren't any time fields at that point anyway.
So, start from the beginning. What kind of data do you have, and what does the event look like?
Why are you using the _time (which indicates when an event was indexed) as the "desired time"?
Pls find below search and sample alerts. I am looking for below output.
Job Name Completion-Time Last-Completion-Time Delay(Completion-Time-Last-Completion-Time)
AJOB Not Yet completed 02-13-2017 9:40
BJOB Not Yet completed 02-13-2017 9:40
CJOB Not Yet completed 02-13-2017 9:40
Would you pls help on below
1) If job hasn't run for today it should show " Not yet completed"
2) How to run same search to get last Last-Completion-Time ( Previous days run)
3) How to get the difference from Previous days run & today's run ( if today's run is not yet completed it should show empty)
host=Host1* OR host=Host2* U0011502 | rex "U0011502 Workflow '(?(?[^.]+).[^.]+.[^.]+.(?[^.]+.[^.]+)[^']+)" | search env=CERT (job=AJob OR job= BJob OR job= C) | eval desired_time=strftime(_time, "%d/%m/%Y %I:%M:%S %p") | table job desired_time | sort desired_time | rename job as "UC4 Job"| rename desired_time as "Completion-Time"
Sample alert :-
Time Event
2/13/17
8:30:40.866 PM 20170213/203040.866 - U0011502 Workflow 'AJob' (RunID '11111') ended normally.
host = host2 source = /xxx/uc4/AutomationEngine/temp/abc.txt sourcetype = uc4
2/13/17
6:29:06.744 PM 20170213/182906.744 - U0011502 Workflow 'BJob' (RunID '222222') ended normally.
host = host1 source = /M/zx/AutomationEngine/temp/def1.txt sourcetype = uc4
Do post some of your sample data.
Initial thoughts on your search:
- drop the "| search" to get host=aa* env=CERT (...)
- table job msg
throws out the desired_time
field you want to use again later on
where is Last-Completion-Time
coming from? are you doing a streamstats
/stats
command to get the last time?
i need some help to get Last-Completion-Time from previous days using same search