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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...