How do I write a search to join these two lines of data and output results in a table with three columns?

[Fergal111]

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

[somesoni2 · Accepted solution]

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

[Fergal111]

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

[somesoni2]

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

[Fergal111]

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!

[bmacias84]

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

[Fergal111]

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.

1. The DateTime from JobProcessStarted and VariantProcessCompleted are logged under both columns instead of their respective headers. Im not quiet sure how to solve this.

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

[bmacias84]

Mind voting the question up if it helped you.

[bmacias84]

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

[jnussbaum_splun]

try ... | transaction JobId | spath | table ProductionId JobProcessStarted VariantProcessCompleted