Hi Splunk heads,
Can you please help me with a really tricky search? I am trying to join the following two lines that have one common field JobId
so I can table the results in 3 columns like this:
ProductionId JobProcessStarted VariantProcessCompleted
2/3709/0115#001 2015-07-30T13:10:33 2015-07-30T13:21:54
Line1:
DateTime=2015-07-30T13:10:33 Event=JobProcessStarted JobId=3AFBD168-D5C9-4E06-B6A8-62FFFE4BCD26
Line2:
DateTime=2015-07-30T13:21:54 Event=VariantProcessCompleted JobId=3AFBD168-D5C9-4E06-B6A8-62FFFE4BCD26 ProductionId=2/3709/0115#001
Is this possible?
Regards,
F
Try one of the options
Using transaction command
your base search giving only events with JobProcessStarted OR VariantProcessCompleted| transaction JobId startswith="Event=JobProcessStarted" endswith="Event=VariantProcessCompleted" | eval JobProcessStarted=mvindex(DateTime,0) | eval VariantProcessCompleted=mvindex(DateTime,1) | table ProductionId JobProcessStarted VariantProcessCompleted
Using stats
your base search giving only events with JobProcessStarted OR VariantProcessCompleted | fields DateTime JobId Event ProductionId | stats values(*) as * by JobId | where mvcount(Event)>1 | eval JobProcessStarted=mvindex(DateTime,0) | eval VariantProcessCompleted=mvindex(DateTime,1) | table ProductionId JobProcessStarted VariantProcessCompleted
Try one of the options
Using transaction command
your base search giving only events with JobProcessStarted OR VariantProcessCompleted| transaction JobId startswith="Event=JobProcessStarted" endswith="Event=VariantProcessCompleted" | eval JobProcessStarted=mvindex(DateTime,0) | eval VariantProcessCompleted=mvindex(DateTime,1) | table ProductionId JobProcessStarted VariantProcessCompleted
Using stats
your base search giving only events with JobProcessStarted OR VariantProcessCompleted | fields DateTime JobId Event ProductionId | stats values(*) as * by JobId | where mvcount(Event)>1 | eval JobProcessStarted=mvindex(DateTime,0) | eval VariantProcessCompleted=mvindex(DateTime,1) | table ProductionId JobProcessStarted VariantProcessCompleted
Thanks very much Somesuni2. Top answer!
Using your stats search, do you think to output the time difference between VariantProcessCompleted and JobProcessStarted and output to a forth column?
Thanks a million for your help!
Cheers,
Fergal
Just add following to end of stats search to get that.
| eval Duration=strptime(VariantProcessCompleted,"%Y-%m-%dT%H:%M:%S") -strptime(JobProcessStarted,"%Y-%m-%dT%H:%M:%S")
Nice one!
Just added a little extra to output Duration in h:m:s instead of seconds
eval Duration=strptime(VariantProcessCompleted,"%Y-%m-%dT%H:%M:%S") -strptime(JobProcessStarted,"%Y-%m-%dT%H:%M:%S") | eval Duration=strftime(Duration,"%H:%M:%S")
Thanks very much for your help!
I think you want to create new fields called JobProcessStarted and VariantProcessCompleted which contain DateTime. Then you can use transaction and table to make a single row. Not sure if spath is needed.
... | eval JobProcessedStarted=if(Event=="JobProcessStarted", DateTime, null()) | eval VariantProcessCompleted=if(Event=="VariantProcessCompleted", DateTime, null() | spath | table ProductionId JobProcessStarted VariantProcessCompleted
Thanks Bernardo!
I am great progress here.
| transaction JobId | eval JobProcessStarted=if(Event=="JobProcessStarted",DateTime,null()) | eval VariantProcessCompleted=if(Event=="VariantProcessCompleted",DateTime,null()) | table ProductionId JobProcessStarted VariantProcessCompleted | sort by DateTime desc
The result currently looks like below. There just lies just two little problems as you can see.
The DateTime from JobProcessStarted and VariantProcessCompleted are logged under both columns instead of their respective headers. Im not quiet sure how to solve this.
There are more than 2 DateTime values per JobId as you can see below (the 5 line below are actually 1 line/event, 1 value under ProductionId, 5 DateTime values under each of the last two columns) They have lines that do not contain EventId=JobProcessStarted or Event=VariantProcessCompleted in the field. They have Event=Error which i do not want to report on. Can I eliminate these timestamps by not searching the lines that contain something like Event=Error or a line that contains something like ID=NONE ?
ProductionId JobProcessStarted VariantProcessCompleted
2/3709/0115#001 2015-07-30T13:10:32 2015-07-30T13:10:32
2015-07-30T13:10:33 2015-07-30T13:10:33
2015-07-30T13:10:43 2015-07-30T13:10:43
2015-07-30T13:21:26 2015-07-30T13:21:26
2015-07-30T13:21:54 2015-07-30T13:21:54
Thanks very much for your help so far:)
Cheers,
F
Mind voting the question up if it helped you.
You will want to place the eval statement before the transaction command.
| eval JobProcessStarted=if(Event=="JobProcessStarted",DateTime,null()) | eval VariantProcessCompleted=if(Event=="VariantProcessCompleted",DateTime,null()) | transaction JobId | table ProductionId JobProcessStarted VariantProcessCompleted | sort by DateTime desc
try ... | transaction JobId | spath | table ProductionId JobProcessStarted VariantProcessCompleted