topic Re: Tranpose Column and Row Headings in Splunk Search

Tranpose Column and Row Headings

IRHM73 — Tue, 29 Mar 2016 11:32:11 GMT

Hi, I wonder whether someone may be able to help me please.

I'm trying to compare the apps set up in my four environments i.e DEV, QA, Staging and PROD.

So for each environment I have written the following query which I will then extract into Excel to compare:

| rest /services/apps/local | search disabled=0 | table label, title, eai:acl.perms.read, eai:acl.perms.write, version
|rename label as "App Name", title as "App Folder", eai:acl.perms.read as "Read Access", eai:acl:perms.write as "Write Access", version as "Version"
|sort +"App Name"

The query works fine, but I'm trying to transpose the table so, that the values in column "A" become he column headers, and the column headers become the row titles.

I've tried using "transpose", "chart over", and "xy series", but I just can't seem to get this to work.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.

Many thanks and kind regards

Chris

Re: Tranpose Column and Row Headings

javiergn — Tue, 29 Mar 2016 12:07:15 GMT

Hi, have you tried

| transpose header_field="column A"

For instance:

| rest /services/apps/local 
| search disabled=0 
| table label, title, eai:acl.perms.read, eai:acl.perms.write, version
| rename label as "App Name", title as "App Folder", eai:acl.perms.read as "Read Access", eai:acl:perms.write as "Write Access", version as "Version"
| sort +"App Name"
| transpose header_field="App Name"

EDIT (version 2):

| rest /services/apps/local 
| search disabled=0 
| table label, title, eai:acl.perms.read, eai:acl.perms.write, version
| rename label as "App Name", title as "App Folder", eai:acl.perms.read as "Read Access", eai:acl:perms.write as "Write Access", version as "Version"
| sort +"App Name"
| transpose 0 header_field="App Name" include_empty=false

Re: Tranpose Column and Row Headings

IRHM73 — Tue, 29 Mar 2016 12:41:31 GMT

Hi, thank you for coming back to me with this.

Yes I have already tried your suggestion, but the problem I have is that it only creates column headings for the first three values from what would have been the values in column A.

Many thanks and kind regards

Chris

Re: Tranpose Column and Row Headings

javiergn — Tue, 29 Mar 2016 13:17:16 GMT

What about this?

| rest /services/apps/local 
| search disabled=0 
| table label, title, eai:acl.perms.read, eai:acl.perms.write, version
| rename label as "App Name", title as "App Folder", eai:acl.perms.read as "Read Access", eai:acl:perms.write as "Write Access", version as "Version"
| sort +"App Name"
| transpose 0 header_field="App Name" include_empty=false

Re: Tranpose Column and Row Headings

IRHM73 — Tue, 29 Mar 2016 13:29:54 GMT

HI, thank you for coming back to me with this.

Your "version2" works great.

Kind Regards

Chris

Re: Tranpose Column and Row Headings

woodcock — Tue, 29 Mar 2016 14:34:28 GMT

Neither one of these works for me on v6.2.3.

Re: Tranpose Column and Row Headings

IRHM73 — Tue, 29 Mar 2016 14:37:14 GMT

Emm strange, works fine on 6.3.3

Regards

Chris