Splunk Search

Transpose Multiple Column Headers

IRHM73
Motivator

HI, I wonder whether someone can help me please.

I'm running the query below which works fine, but I'm having some difficulty with the formatting.

(wso2_wmf(RequestCompleted)) OR (auth_wmf(RequestReceived))
| spath output=afin input=detail.responseMessage path=affinityGroup
| eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
| stats values(afin) as afin, values(request.detail.apiContext) as api by requestid
| search (api="benefits" OR api="employment" OR api="income")
| stats count by afin, api
| transpose header_field=api

I'd like the output to be as per the enclosed screenshot. So the "api" field is row1, the "afin" field is row 2 with the metrics at row 3.

I'm able to transpose the "api" data to column headers but I can't find a way to create a secondary set of column headers using the "afin" data.

Could someone possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

Chris

alt text

1 Solution

IRHM73
Motivator

All,

I've come up with the following which resolves the issue highlighted in this ticket.

 (wso2_wmf(RequestCompleted)) OR (`auth_wmf(RequestReceived)`)
 | spath output=afin input=detail.responseMessage path=affinityGroup
 | eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
 | stats values(afin) as afin, values(request.detail.apiContext) as api by requestid
 | search (api="benefits" OR api="employment" OR api="income")
 | stats count by afin, api
 | transpose 0
| fields - column

Many thanks for all your help.

Regards

Chris

View solution in original post

0 Karma

IRHM73
Motivator

All,

I've come up with the following which resolves the issue highlighted in this ticket.

 (wso2_wmf(RequestCompleted)) OR (`auth_wmf(RequestReceived)`)
 | spath output=afin input=detail.responseMessage path=affinityGroup
 | eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
 | stats values(afin) as afin, values(request.detail.apiContext) as api by requestid
 | search (api="benefits" OR api="employment" OR api="income")
 | stats count by afin, api
 | transpose 0
| fields - column

Many thanks for all your help.

Regards

Chris

0 Karma

cmerriman
Super Champion

You can't have two headers in Splunk like you can in Excel. The best you can do is create a field with the two columns concatenated. something like:

(wso2_wmf(RequestCompleted)) OR (`auth_wmf(RequestReceived)`)
| spath output=afin input=detail.responseMessage path=affinityGroup
| eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
| stats values(afin) as afin, values(request.detail.apiContext) as api by requestid
| search (api="benefits" OR api="employment" OR api="income")
| stats count by afin, api
| eval transpose_field=afin." - ".api|fields - afin api
| transpose header_field=transpose_field
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...