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

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

nickhills — Thu, 17 Dec 2015 08:26:41 GMT

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.

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

jmallorquin — Thu, 17 Dec 2015 08:55:38 GMT

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

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

nickhills — Thu, 17 Dec 2015 10:43:51 GMT

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.

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

Runals — Thu, 17 Dec 2015 12:36:23 GMT

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.

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

mkhiani_splunk — Sun, 19 Feb 2017 16:09:08 GMT

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