topic Re: Converting unique IDs into chart column names in Splunk Search

Converting unique IDs into chart column names

jimmymccauley — Fri, 22 Mar 2019 14:27:36 GMT

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

Re: Converting unique IDs into chart column names

renjith_nair — Sat, 23 Mar 2019 12:01:58 GMT

@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

Re: Converting unique IDs into chart column names

jimmymccauley — Mon, 25 Mar 2019 08:52:54 GMT

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... 🙂

Re: Converting unique IDs into chart column names

jimmymccauley — Mon, 25 Mar 2019 08:54:17 GMT

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

Re: Converting unique IDs into chart column names

renjith_nair — Mon, 25 Mar 2019 09:21:30 GMT

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