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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...