Splunk Search

How to get out in desired format

harsush
Path Finder

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

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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"?

0 Karma

harsush
Path Finder

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

cmerriman
Super Champion

where is Last-Completion-Time coming from? are you doing a streamstats/stats command to get the last time?

0 Karma

harsush
Path Finder

i need some help to get Last-Completion-Time from previous days using same search

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...