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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...