Splunk Search

Joining two records from a csv file based on a column

madakkas
Explorer

I am working on a monitoring tool where in I have to monitor the job completion and calculate the estimates in accordance.

Till now I have kind of been able to capture the start and endtime of the jobs as below and have written them to a file as job_monitor.csv. I do this search repeatedly evey two minutes and append the relevant jobs.

sl_no,JOBNAME,START_TIME,END_TIME
1,S3,,15.51.42
2,S2,,15.21.35
3,J3,,14.52.28
4,J2,,14.51.22
5,S1,,15.01.28
6,J1,,14.31.02
7,S3,15.21.42,
8,S2,15.01.34,
9,S1,14.51.28,
10,J3,14.51.28,
11,J2,14.31.22,
12,J1,14.30.02,

Once the above details are captured, I am looking to convert this into a below format. There could be jobs with same JOBNAME, but two jobs cannot run in parallel, ie , if there is an entry for start_time , then the next End_time is relevant to the same job itself. I am not able to map these to look as below.

sl_no,JOBNAME,START_TIME,END_TIME
1,S3,15.21.42,15.51.42
2,S2,15.01.34,15.21.35
3,J3,14.51.28,14.52.28
4,J2,14.31.22,14.51.22
5,S1,14.51.28,15.01.28
6,J1,14.30.02,14.31.02

Any guidance would be appreciated. Thank You in Advance to all here.

Tags (3)
0 Karma
1 Solution

mayurr98
Super Champion

You can try something like this

<your_base_search> | stats values(START_TIME) as START_TIME values(END_TIME) as END_TIME by JOBNAME

View solution in original post

0 Karma

mayurr98
Super Champion

You can try something like this

<your_base_search> | stats values(START_TIME) as START_TIME values(END_TIME) as END_TIME by JOBNAME
0 Karma

FrankVl
Ultra Champion

Any specific reason to do it like this, instead of suggesting to use the transaction command?

Especially since he states that job names can be re-used (but not in parallel), using transactions may give more accurate results than using stats values()..., right?

0 Karma

madakkas
Explorer

The stats solution did work. thank You for that. I just appended it with some more where clause to meet my demand.

thank You mayurr98

0 Karma

mayurr98
Super Champion

stats is any time better than transaction command I gave this solution considering performance. I think even this would give accurate results as long as there is a unique start_time and end_time for a specific jobname
because I could see from the table there are only two records for a specific jobname.

0 Karma

madakkas
Explorer

Just to add on , there cuuld be a situation where in the job has started and has not completed, In that case I would need the record as below

sl_no,JOBNAME,START_TIME,END_TIME
1,S3,15.21.42,

which would let me know that the job has started but not completed yet. thanks again.

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