Splunk Search

Creating a Matrix/Grid

watsm10
Communicator

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!

0 Karma
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

mamta11
New Member

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

By this you can rename your column headers

0 Karma

kristian_kolb
Ultra Champion

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

watsm10
Communicator

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 🙂

0 Karma

HattrickNZ
Motivator

@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
0 Karma
Get Updates on the Splunk Community!

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...

Cloud Platform | Discontinuing support for TLS version 1.0 and 1.1

Overview Transport Layer Security (TLS) is a security communications protocol that lets two computers, ...