Splunk Search
Highlighted

How do I create a table result with "stats count by ‘field’"?

Ultra Champion

I have a set of events which have multiple values for a single field such as:

accountName=customerA result=[passed|failed|error|delayed]

I can obtain the statistical result of these results using:

stats count by result, accountName

which gives me up to 4 rows per customer with the count of relevant events. However, I would like to tabulate this data to make it more readable:

|           | Pass | Failed | Error | Delayed |
| CustomerA | 100  | 10     | 12    | 10      |
| CustomerB | 200  | 5      |       | 20      |
| CustomerC | 50   | 6      | 3     | 30      |

Is there a simple method to achieve this? I am sure i must be missing something obvious.

0 Karma
Highlighted

Re: How do I create a table result with "stats count by ‘field’"?

Builder

Hi
Could you try this?

|rex "\|(?<customer>[^\|]+\|(?<pass>[^\|]+\|(?<failed>[^\|]+\|(?<error>[^\|]+\|(?<delayed>[^\|]+"
|stats values(pass) as pass values(failed) as failed values(error) as error values(delayed) as delayed by customer
0 Karma
Highlighted

Re: How do I create a table result with "stats count by ‘field’"?

Ultra Champion

so far, I am using:

|eval Pass=if(result="pass",1,0) |eval Failed=if(result="failed",1,0)|eval Error=if(result="error",1,0)|stats count as Total sum(Pass) as Pass sum(Failed) as Failed sum(Error) as Error by customer

Which gives the desired output, but I can't help but think there could be an easier way.

0 Karma
Highlighted

Re: How do I create a table result with "stats count by ‘field’"?

Motivator

Instead of stats use chart

accountName=* results=* | chart count over result by accountName

You might have to reverse the order and by fields as I often flip those mentally =). The only real gottcha to chart (or timechart for that matter) is if you use any commands after the chart command fields like count don't exist anymore as they would with stats. In your case after the chart command the fields you would have would be accountName, Pass, Failed, Error, Delayed. I mention that only because you will have to take that into account if you want to do things like sort the results.

Highlighted

Re: How do I create a table result with "stats count by ‘field’"?

Splunk Employee
Splunk Employee

I think you might want to use xyseries as your final command where you write:
xyseries accountName,result,count

So you'd have something like:
yourInitialSearch | stats count by result, accountName | xyseries accountName,result,count