topic Re: subsearch to combine multiple queries and sum up in Splunk Search

subsearch to combine multiple queries and sum up

vanakkam — Sun, 18 Aug 2019 04:33:06 GMT

example log data

project_name=abc category=xyz job_id=1 stage_begin=compile time=2019-08-16 15:00:00
project_name=abc category=xyz job_id=1 stage_end=compile   time=2019-08-16 15:03:00
project_name=abc category=xyz job_id=1 stage_begin=build     time=2019-08-16 15:03:00
project_name=abc category=xyz job_id=1 stage_end=build         time=2019-08-16 15:06:00
project_name=abc category=xyz job_id=1 stage_begin=artifacts time=2019-08-16 15:07:00
project_name=abc category=xyz job_id=1 stage_end=artifacts    time=2019-08-16 15:12:00
project_name=abc category=xyz job_id=1 stage_begin=upload    time=2019-08-16 15:13:00
project_name=abc category=xyz job_id=1 stage_end=upload       time=2019-08-16 15:14:00

Need a output table like this

project_name Category job_id compile_time build_time . artifacts_time  upload_time
abc                         xyz         1                3mins .     3 mins .          5 mins .           1min
...
...
...

i tried to have a query to do stage begin and then join with job_id and get a query for stage_end which I can do only for 1 stage so I am not sure how to get all stages for a given job_id. I tried appendcols but it messes up when some job_ids don't have one of the fields or it fails after build, etc.

Need help to get a report similar to the sample
Thanks for the help.

Re: subsearch to combine multiple queries and sum up

Sukisen1981 — Sun, 18 Aug 2019 15:17:03 GMT

why are you trying to use a join command? are these events from different sources / indexes
Also, can a different job id have more stages than compile,build,upload and artifact?

Re: subsearch to combine multiple queries and sum up

Kawtar — Sun, 18 Aug 2019 18:27:08 GMT

Hello,

Did you try this:
index=* .... | appendpipe [ search index=* ...]

Re: subsearch to combine multiple queries and sum up

woodcock — Sun, 18 Aug 2019 20:12:00 GMT

Like this:

| makeresults
| eval raw="project_name=abc,category=xyz,job_id=1,stage_begin=compile,time=2019-08-16T15:00:00:::project_name=abc,category=xyz,job_id=1,stage_end=compile,time=2019-08-16T15:03:00:::project_name=abc,category=xyz,job_id=1,stage_begin=build,time=2019-08-16T15:03:00:::project_name=abc,category=xyz,job_id=1,stage_end=build,time=2019-08-16T15:06:00:::project_name=abc,category=xyz,job_id=1,stage_begin=artifacts,time=2019-08-16T15:07:00:::project_name=abc,category=xyz,job_id=1,stage_end=artifacts,time=2019-08-16T15:12:00:::project_name=abc,category=xyz,job_id=1,stage_begin=upload,time=2019-08-16T15:13:00:::project_name=abc,category=xyz,job_id=1,stage_end=upload,time=2019-08-16T15:14:00"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval _time = strptime(time, "%Y-%m-%dT%H:%M:%S")

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval stage = coalesce(stage_begin, stage_end)
| fields - stage_begin stage_end
| stats range(_time) AS duration BY project_name category job_id stage
| eval {stage}_time = duration
| fields - stage duration
| stats values(*) AS * BY project_name category job_id
| table project_name category job_id compile_time build_time . artifacts_time upload_time *

Re: subsearch to combine multiple queries and sum up

vanakkam — Sun, 18 Aug 2019 22:36:17 GMT

Thanks a lot Woodcock! that worked perfectly !

Re: subsearch to combine multiple queries and sum up

vanakkam — Sun, 18 Aug 2019 22:40:34 GMT

One follow up question. can you explain this query for a new splunker like me. I can then build on top of it for future queries like this.

Re: subsearch to combine multiple queries and sum up

woodcock — Mon, 19 Aug 2019 02:16:51 GMT

The trick is to remove pipes from the end on back until you completely understand it and then add commands/pipes back one-by-one and see what each one does. The only tricky thing is the curly-brace line which takes the cale of the braces field and make it the name of a new field.

Re: subsearch to combine multiple queries and sum up

vanakkam — Wed, 30 Sep 2020 01:49:29 GMT

Thanks Woodcock for the explanation. I need one last column the total time for each job. i am not sure i i tried to remove table command line and replace that line with
| stats sum(compile_time+build_time+artifacts_time+upload_time) as TotalTime BY project_name category job_id
i got a table with project_name category job_id all with value and Empty column TotalTime for each row.

Re: subsearch to combine multiple queries and sum up

Sukisen1981 — Mon, 19 Aug 2019 10:27:24 GMT

add this after the table command, that is after all the code above
| addtotals
|eval Total=Total-job_id
| eval Totalinmins=round((Total/60),2)

I have subtracted the job_id as it is a numerical field in the dummy data,please double check

Re: subsearch to combine multiple queries and sum up

vanakkam — Mon, 19 Aug 2019 17:26:08 GMT

That worked Thanks Sukisen

Re: subsearch to combine multiple queries and sum up

Sukisen1981 — Mon, 19 Aug 2019 17:36:00 GMT

glad it worked @vanakkam
Please upvote my comment if it helped you significantly