Splunk Search

Converting unique IDs into chart column names

jimmymccauley
Explorer

Hi All

This is my second SOS this week as I get acquainted with Splunk. I've exhausted all possibilities trying to solve this problem... I'm part way there and I'd hoping someone can provide inspiration or enlightenment.

I'm attempting to create a dashboard that shows the build outcome for a number of services over the last 5 builds executed.
The query I use to extract and tabulate the raw data is as follows:
index="osesat-dev-01" source=fluentd environment="test-b" platform_region="eu-west-1" kubernetes.pod_name="trufflehog*" OR kubernetes.pod_name="sat-ctrlr-*" app_name=* request_uuid="*"
| rex field=artifact_source ".*/(?.*)"
| fields - _raw
| fields _time, request_uuid, app_name, svc_name, artifact_source, artifact_branch, build_id, kubernetes.pod_name, test_type, test_tool
| join request_uuid [search index="osesat-dev-01" source=fluentd environment="test-b" platform_region="eu-west-1" request_uuid="*" kubernetes.pod_name="trufflehog*" OR kubernetes.pod_name="sat-ctrlr-*" result="success" OR result="fail"
| fields - _raw
| fields _time, request_uuid, artifact_source, artifact_branch, build_id, result, kubernetes.pod_name, report, "High Entropy count"]
| dedup 5 app_name
| table app_name, svc_name, build_id, result
| sort -time

My problem is when it comes to charting the 5 most recent builds. I'd like 5 columns called:
"Latest Build", "Latest Build -1"....... "Latest Build -4".
Under each of these columns, we'd see a 'Success' or 'Fail' status with a separate row for each service.

However the Build ID fields contain Build IDs specific to each service e.g. 5 most recent builds for service 1 and service 2 could be as follows:
Service 1: Build 119, Build 118, Build 117, Build 116, Build 115
Serivce 2: Build 20, Build 19, Build 18, Build 17, Build 16

I am having a lot of difficultly trying to reconcile the unique Build IDs to the 5 columns representing the most recent build history.

When I use "Chart over by" or "xyseries" I get 10 columns if I chart the Service 1 and Service 2 example above - I can't figure out how to rename the columns.
When I use "transpose", I can rename the columns; I can get the outcome I want when I report build history against a single service, but I have problems when I try to chart build history for multiple services.

Please see the below which provides an overview of the data table produced
Service Name | Build ID | Build Status
--------------------|------------|--------------
webgoat | Build 20 | fail

webgoat | Build 19 | success

webgoat | Build 18 | fail

webgoat | Build 17 | fail

webgoat | Build 16 | fail

pipeline-svc | Build 119 | success

pipeline-svc | Build 118 | success

pipeline-svc | Build 117 | fail

pipeline-svc | Build 116 | fail

pipeline-svc | Build 115 | success

Any workarounds greatly appreciated.

Thanks,

jimmy

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@jimmymccauley ,

Looking at your search, not quite sure why you need a join since both searches in the join have similar search terms. Most probably your existing search could be rewritten as below. Please have a look and see if it yields the same result

index="osesat-dev-01" source=fluentd environment="test-b" platform_region="eu-west-1" request_uuid="*" 
 kubernetes.pod_name="trufflehog*" OR kubernetes.pod_name="sat-ctrlr-*" result="success" OR result="fail" 
 |fields _time, app_name, svc_name, build_id, result | sort -time|dedup 5 app_name

Coming back to the original question, try adding the below search to your original search.

version 1

 |untable svc_name,build_id,result | eval buildNo=if(match(result,"build*"),result,null())|filldown buildNo
 |streamstats count by svc_name|eval buildName=if(count%2==0,"Build".(count-1),"Build".count)
 |chart values(status) over svc by buildName

If you really want to split the rows for build id and status instead of multivalue field, try this version

Version 2

 |untable svc_name,build_id,result | eval buildNo=if(match(result,"build*"),result,null())|filldown buildNo
 |streamstats count by svc_name|eval buildName=if(count%2==0,"Build".(count-1),"Build".count)
 |chart values(status) over svc by buildName
 |eval z=mvzip(mvzip(mvzip(mvzip(Build1,Build3),Build5),Build7),Build9)|fields svc,z|mvexpand z
 |rex field=z "(?<LatestBuild>\w+),(?<LatestBuild_1>\w+),(?<LatestBuild_2>\w+),(?<LatestBuild_3>\w+),(?<LatestBuild_4>\w+)"
 |fields - z   

Sample data and search used to test

|makeresults|eval svc_name="webgoat webgoat webgoat webgoat webgoat pipeline-svc pipeline-svc pipeline-svc  pipeline-svc pipeline-svc"|makemv svc_name|mvexpand svc_name
|appendcols [| makeresults |eval build_id="build_id20 build_id19 build_id18 build_id17 build_id16 build_id119 build_id118 build_id117 build_id116 build_id115"|makemv build_id| mvexpand build_id]
|appendcols [|makeresults |eval result="fail success fail fail fail success success fail fail success" | makemv result|mvexpand result]
|table svc_name,build_id,result
|rename COMMENT as "<--Until here is dummy data generation and has nothing to do with original search-->"
|untable  svc_name,build_id,result|eval build_idno=if(match(result,"build_id*"),result,null())|filldown build_idno
|streamstats count by svc_name|eval build_idName=if(count%2==0,"build_id".(count-1),"build_id".count)
|chart values(result) over svc_name by build_idName
|eval z=mvzip(mvzip(mvzip(mvzip(build_id1,build_id3),build_id5),build_id7),build_id9)|table svc_name,z
|mvexpand z|rex field=z "(?<Latestbuild>\w+),(?<Latestbuild_1>\w+),(?<Latestbuild_2>\w+),(?<Latestbuild_3>\w+),(?<Latestbuild_4>\w+)"|fields - z

alt text

Happy Splunking!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@jimmymccauley ,

Looking at your search, not quite sure why you need a join since both searches in the join have similar search terms. Most probably your existing search could be rewritten as below. Please have a look and see if it yields the same result

index="osesat-dev-01" source=fluentd environment="test-b" platform_region="eu-west-1" request_uuid="*" 
 kubernetes.pod_name="trufflehog*" OR kubernetes.pod_name="sat-ctrlr-*" result="success" OR result="fail" 
 |fields _time, app_name, svc_name, build_id, result | sort -time|dedup 5 app_name

Coming back to the original question, try adding the below search to your original search.

version 1

 |untable svc_name,build_id,result | eval buildNo=if(match(result,"build*"),result,null())|filldown buildNo
 |streamstats count by svc_name|eval buildName=if(count%2==0,"Build".(count-1),"Build".count)
 |chart values(status) over svc by buildName

If you really want to split the rows for build id and status instead of multivalue field, try this version

Version 2

 |untable svc_name,build_id,result | eval buildNo=if(match(result,"build*"),result,null())|filldown buildNo
 |streamstats count by svc_name|eval buildName=if(count%2==0,"Build".(count-1),"Build".count)
 |chart values(status) over svc by buildName
 |eval z=mvzip(mvzip(mvzip(mvzip(Build1,Build3),Build5),Build7),Build9)|fields svc,z|mvexpand z
 |rex field=z "(?<LatestBuild>\w+),(?<LatestBuild_1>\w+),(?<LatestBuild_2>\w+),(?<LatestBuild_3>\w+),(?<LatestBuild_4>\w+)"
 |fields - z   

Sample data and search used to test

|makeresults|eval svc_name="webgoat webgoat webgoat webgoat webgoat pipeline-svc pipeline-svc pipeline-svc  pipeline-svc pipeline-svc"|makemv svc_name|mvexpand svc_name
|appendcols [| makeresults |eval build_id="build_id20 build_id19 build_id18 build_id17 build_id16 build_id119 build_id118 build_id117 build_id116 build_id115"|makemv build_id| mvexpand build_id]
|appendcols [|makeresults |eval result="fail success fail fail fail success success fail fail success" | makemv result|mvexpand result]
|table svc_name,build_id,result
|rename COMMENT as "<--Until here is dummy data generation and has nothing to do with original search-->"
|untable  svc_name,build_id,result|eval build_idno=if(match(result,"build_id*"),result,null())|filldown build_idno
|streamstats count by svc_name|eval build_idName=if(count%2==0,"build_id".(count-1),"build_id".count)
|chart values(result) over svc_name by build_idName
|eval z=mvzip(mvzip(mvzip(mvzip(build_id1,build_id3),build_id5),build_id7),build_id9)|table svc_name,z
|mvexpand z|rex field=z "(?<Latestbuild>\w+),(?<Latestbuild_1>\w+),(?<Latestbuild_2>\w+),(?<Latestbuild_3>\w+),(?<Latestbuild_4>\w+)"|fields - z

alt text

Happy Splunking!
0 Karma

jimmymccauley
Explorer

Renjith, thanks very much for such a supremely detailed answer. I came across streamstats on Friday evening and thought that would present a way forward - you've really helped me accelerate towards achieving what I need to! I do also think the events we are generating and the data we are indexing could do with some revision, which would make life easier... 🙂

jimmymccauley
Explorer

Sorry, I am also limited in the Karma I can give at the moment! I'll pay it forward as I progress

renjith_nair
SplunkTrust
SplunkTrust

Hi Jimmy (@jimmymccauley), glad that it worked!. Its alright about Karma :-). If you want to give points in a post , you can upvote the answers and comments

Happy Splunking!
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 ...