topic Re: Creating a Matrix/Grid in Splunk Search

Creating a Matrix/Grid

watsm10 — Tue, 23 Oct 2012 16:13:38 GMT

Hi Splunkers,

I've been asked to create a command centre for our business. The main requirement is to have a single dashboard with a table in the form of a grid where we have counts of transactions, success/failure rates etc... per user. I'm having trouble with the layout of the matrix..

Here is the requirement for how it should look:



USER                   | User 1 | User 2 | User 3 | User 4 | User 5 |

Total Volume of Quotes | 100456 | 675890 | 573747 | 235478 | 138542 |

Success %              | 99%    | 98%    | 97%    | 99%    | 95%    |

I've tried xyseries, but I cannot get the right format.

Can anyone offer some advice?

Thanks!

Re: Creating a Matrix/Grid

kristian_kolb — Tue, 23 Oct 2012 20:25:11 GMT

Since I don't have your data (you seem to have forgotten to add some), I had to make do with the ever interesting access_combined logs.

sourcetype=access_combined clientip=12* status=2* OR status=5* 
| head 1000 
| chart count over clientip by status_description 
| addtotals 
| eval "Success Ratio" = round((OK / Total * 100),0) . "%" 
| fields - OK, "Service Unavailable" 
| transpose

The first two rows in the example above is to get a limited number of events with typical success/fail content.

Then you create a chart with clientip's (users) on one axis, and the success/fail status on the other.

Add the successes to the fails to get a Total

Find out the ratio of success to Total, and make a nice string with percent sign.

Transpose, i.e. shift columns to row and vice versa.

My result:

column            row1           row2          row3
clientip          12.23.34.45    12.13.14.15   12.33.44.55
Success Ratio     87%            79%           82%
Total             1243           5564          32331

Hope this helps,

Kristian

Re: Creating a Matrix/Grid

watsm10 — Wed, 24 Oct 2012 08:19:05 GMT

Hi Kristian,
Thanks for your help. That is exactly what I was after. Only thing is, I want to raise the first row of the results to become the column headers so that user 1 is Row 1, user 2 is Row 2 etc. Any ideas on how to do this? Thanks 🙂

Re: Creating a Matrix/Grid

mamta11 — Fri, 14 Jun 2013 05:24:57 GMT

....|Rename "row 1" as user1 |Rename "row 2" as user2|Rename "row 3" as user3

By this you can rename your column headers

Re: Creating a Matrix/Grid

HattrickNZ — Wed, 08 Jul 2015 23:45:29 GMT

@kristian.kolb - how do I remove this

column            row1           row2          row3

and just have this:

 clientip          12.23.34.45    12.13.14.15   12.33.44.55
 Success Ratio     87%            79%           82%
 Total             1243           5564          32331