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

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

Fergal111 — Thu, 30 Jul 2015 14:53:40 GMT

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

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

jnussbaum_splun — Thu, 30 Jul 2015 15:59:34 GMT

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

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

bmacias84 — Thu, 30 Jul 2015 16:16:49 GMT

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

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

Fergal111 — Thu, 30 Jul 2015 18:49:48 GMT

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

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

somesoni2 — Thu, 30 Jul 2015 19:29:04 GMT

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

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

bmacias84 — Thu, 30 Jul 2015 19:45:21 GMT

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

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

bmacias84 — Thu, 30 Jul 2015 19:51:17 GMT

Mind voting the question up if it helped you.

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

Fergal111 — Thu, 30 Jul 2015 20:58:20 GMT

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

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

somesoni2 — Thu, 30 Jul 2015 21:31:15 GMT

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

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

Fergal111 — Thu, 30 Jul 2015 22:11:28 GMT

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!