Query Pivot multiple columns

[MrJohn230]

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:

_time	Resource	CurValue	Category	*NewColumn
2023-11-06	Care	14.20	Red	10
2023-11-06	Cover	3.4	Red	3
2023-11-06	NonCover	5.5	Red	8

[MrJohn230]

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")

[ITWhisperer · Accepted solution]

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]

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