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
Path Finder

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

Tags (2)
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Path Finder

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

0 Karma

somesoni2
Revered Legend

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
Path Finder

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!

0 Karma

bmacias84
Champion

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
Path Finder

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

0 Karma

bmacias84
Champion

Mind voting the question up if it helped you.

0 Karma

bmacias84
Champion

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
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...