Splunk Search

subsearch to combine multiple queries and sum up

vanakkam
New Member

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.

Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

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 *

View solution in original post

woodcock
Esteemed Legend

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 *

vanakkam
New Member

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

vanakkam
New Member

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.

0 Karma

Sukisen1981
Champion

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

0 Karma

vanakkam
New Member

That worked Thanks Sukisen

0 Karma

Sukisen1981
Champion

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

0 Karma

vanakkam
New Member

Thanks a lot Woodcock! that worked perfectly !

0 Karma

Sukisen1981
Champion

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?

0 Karma

Kawtar
Path Finder

Hello,

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

0 Karma
Get Updates on the Splunk Community!

Set Up More Secure Configurations in Splunk Enterprise With Config Assist

This blog post is part 3 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Observability Highlights | November 2022 Newsletter

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

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...