Dashboards & Visualizations

Need help to create a transpose of a table

Mrig342
Communicator

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

TransactionStatuscount(Status)
A200 OK45
A400 Bad Request20
B200 OK110
B400 Bad Request15
B500 Internal Server Error5
C200 OK85
C400 Bad Request25
C500 Internal Server Error30

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

Transaction200 OK400 Bad Request500 Internal Server ErrorTotal
A4520065
B110155130
C852530140

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

As I said, you may need the fillnull command

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

View solution in original post

Gr0und_Z3r0
Communicator

Hi @Mrig342 ,

This will do the trick.

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



Gr0und_Z3r0_0-1650452331113.png

 

Mrig342
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

As I said, you may need the fillnull command

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

gcusello
Legend

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
Communicator

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:

TransactionTotal200 OK400 Bad Request500 Internal Server Error
A19712 
B909 
C110 

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Legend

Hi @Mrig342,

did you tried the hint from @ITWhisperer ?

| fillnull value=0

Ciao.

Giuseppe

gcusello
Legend

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
SplunkTrust
SplunkTrust

@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
Legend

Hi 

I am wrong every time by reversing OVER and BY 😉

Ciao.

Giuseppe

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

0 Karma