Transpose Multiple Column Headers

[IRHM73]

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

[IRHM73 · Accepted solution]

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

[cmerriman]

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