Splunk Search

Query Pivot multiple columns

MrJohn230
Path Finder

Hello, I have the below code. I'm trying to create a new column that extracts and pivots CareCnts, CoverCnts, NonCoverCnts, etc... (There are more but I have simplified). These |eval are related to their corresponding `| evals`. 

New Columns = ResourceCounts

How would one accomplish this? 

index=red msg="*COMPLETED Red*"
| spath output=logMessage path=msg
| eval Care=spath(json, "Info.Care.elapsedTime")
| eval CareCnts=spath(json, "Info.Care.Redcount")
| eval Cover=spath(json, "Info.Cover.elapsedTime")
| eval CoverCnts=spath(json, "Info.Cover.Redcount")
| eval NonCover=spath(json, "Info.NonCover.elapsedTime")
| eval NonCoverCnts=spath(json, "Info.NonCover.Redcount")
| eval Category = "Red"
| table _time, Care, Cover, NonCover, Category
| eval SysTime = Category + ":" + _time
| fields - Category
| untable SysTime Resource CurValue
| eval Category = mvindex(split(SysTime, ":"), 0)
| eval _time = mvindex(split(SysTime, ":"), 1)
| fields - SysTime
| table _time, Resource, CurValue, Category

Example output:

_timeResourceCurValueCategory*NewColumn
2023-11-06Care14.20Red10
2023-11-06 Cover3.4Red3
2023-11-06 NonCover5.5Red8

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

That's three different fields, which you aren't including in your table command (so that would be dropped). Perhaps you should consider concatenating the counts and the elapse times (much like you did with the category and time) before the untable, then, splitting them out again later?

View solution in original post

MrJohn230
Path Finder

It's derived through the | eval 

| eval CareCnts=spath(json, "Info.Care.Redcount")
| eval CoverCnts=spath(json, "Info.Cover.Redcount")
| eval NonCoverCnts=spath(json, "Info.NonCover.Redcount")
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

That's three different fields, which you aren't including in your table command (so that would be dropped). Perhaps you should consider concatenating the counts and the elapse times (much like you did with the category and time) before the untable, then, splitting them out again later?

ITWhisperer
SplunkTrust
SplunkTrust

How is NewColumn derived, especially since you haven't included CareCnts, CoverCnts and NonCoverCnts in your first table command?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...