topic Re: Need help to create a transpose of a table in Dashboards & Visualizations

Need help to create a transpose of a table

Mrig342 — Wed, 20 Apr 2022 08:00:04 GMT

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.

Re: Need help to create a transpose of a table

gcusello — Wed, 20 Apr 2022 08:07:30 GMT

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

Re: Need help to create a transpose of a table

ITWhisperer — Wed, 20 Apr 2022 08:23:30 GMT

@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

Re: Need help to create a transpose of a table

gcusello — Wed, 20 Apr 2022 08:47:33 GMT

I am wrong every time by reversing OVER and BY 😉

Ciao.

Giuseppe

Re: Need help to create a transpose of a table

ITWhisperer — Wed, 20 Apr 2022 09:01:15 GMT

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

Re: Need help to create a transpose of a table

Mrig342 — Wed, 20 Apr 2022 10:22:05 GMT

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.

Re: Need help to create a transpose of a table

gcusello — Wed, 20 Apr 2022 10:27:34 GMT

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 😉

Re: Need help to create a transpose of a table

ITWhisperer — Wed, 20 Apr 2022 10:35:15 GMT

As I said, you may need the fillnull command

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

Re: Need help to create a transpose of a table

Mrig342 — Wed, 20 Apr 2022 10:43:21 GMT

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.

Re: Need help to create a transpose of a table

gcusello — Wed, 20 Apr 2022 10:49:27 GMT

Hi @Mrig342,

did you tried the hint from @ITWhisperer ?

| fillnull value=0

Ciao.

Giuseppe

Re: Need help to create a transpose of a table

ITWhisperer — Wed, 20 Apr 2022 10:53:42 GMT

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.

Re: Need help to create a transpose of a table

Gr0und_Z3r0 — Wed, 20 Apr 2022 10:59:09 GMT

Hi @Mrig342 ,

This will do the trick.

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