Splunk Search

Joining two records from a csv file based on a column

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

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

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

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

SplunkTrust
SplunkTrust

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!