Need help to create a transpose of a table

[Mrig342]

I have created a table as below  using the query  index=xyz | stats count(Status) as Total by Transaction,Status

Transaction	Status	count(Status)
A	200 OK	45
A	400 Bad Request	20
B	200 OK	110
B	400 Bad Request	15
B	500 Internal Server Error	5
C	200 OK	85
C	400 Bad Request	25
C	500 Internal Server Error	30

But I want to get a transpose of the table as below:

Transaction	200 OK	400 Bad Request	500 Internal Server Error	Total
A	45	20	0	65
B	110	15	5	130
C	85	25	30	140

Please help me to create a query to get the desired output.

[Gr0und_Z3r0]

Hi @Mrig342 ,

This will do the trick.

| inputlookup Test1.csv 
| chart sum(count) by Transaction Status
| fillnull value=0
| addtotals

[Mrig342]

Thank you very much @ITWhisperer & @gcusello for you help.

What if I want to keep the "Total" column in second place just after "Transaction" column and not at the last. How to modify it..? I tried to use table command but that keeps the null fields empty and not with a zero.

[ITWhisperer · Accepted solution]

As I said, you may need the fillnull command

| chart count OVER Transaction by Status
| fillnull value=0
| addtotals
| table Transaction Total *

[gcusello]

Hi @Mrig342,

if you want to change the column order, you have to add a table command at the end of your search:

 index=xyz 
| chart count OVER Transaction BY Status
| addtotals
| table Transaction Total "200 OK" "400 Bad Request" "500 Internal Server Error"	

if one of the answers solves your need, please accept it for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

[Mrig342]

Hi @gcusello 

I used the table command. But if there no value for a time range then it doesn't fill the null values with zero.  For example: if I change the time range to last 15 minutes the table becomes like below:

Transaction	Total	200 OK	400 Bad Request	500 Internal Server Error
A	19	7	12
B	9	0	9
C	1	1	0

 Please help to find a way to keep the values as zero for null fields.

[ITWhisperer]

This is another classic problem of trying to get splunk to report on something that didn't happen (or at least isn't in the set of events in the pipeline). If there are no 500 errors in the timeframe, you won't get them reported on, unless you artificially inject dummy zero counts for the status errors you are expecting.

[gcusello]

Hi @Mrig342,

did you tried the hint from @ITWhisperer ?

| fillnull value=0

Ciao.

Giuseppe

[gcusello]

Hi @Mrig342,

did you explored the chart command? for more infos see at https://docs.splunk.com/Documentation/Splunk/8.2.6/SearchReference/Chart 

please try something like this:

 index=xyz 
| chart count OVER Status BY Transaction
| addtotals

Ciao.

Giuseppe

[ITWhisperer]

@gcusello is almost correct, the OVER and BY fields should be swapped, and you might also need a fillnull

| chart count OVER Transaction by Status
| fillnull value=0
| addtotals

[gcusello]

Hi 

I am wrong every time by reversing OVER and BY 😉

Ciao.

Giuseppe

[ITWhisperer]

Personally, I don't use OVER; the first field of BY is the first column and provides the x-axis in line and column charts for example, the second field of BY provides the remaining column headers and provides the series names e.g. this could have been written as

| chart count by Transaction Status
| fillnull value=0
| addtotals