Splunk create table with counts based on differing value in a field

user_19895326 0
Viewed 105

I am trying to create a table which counts the items in my list with splunk.

E.g. I have a list of items, with one item having the following fields:

  • name
  • type
  • result (e.g. has only three values success, failure, N.A.)

I wish to create a table that groups the items into its respective names, and then count the number of items belong to that name and list the respective type of the group which contains the set of items. After which, I wish to have additional columns that split the counts into different columns based on the "tag" attribute.

Here's a sample table format I wish to achieve:

name      | type       | success | failure | N.A. | Total count
Item A    | fruits     |    5    |   0     |   1  |     6
Item B    | vegetables |    0    |   2     |   3  |     5
Item C    | sweets     |    1    |   3     |   2  |     6

Here's what I tried after looking up in the splunk command reference:

index="The index I am looking for" | stats count, values(fields.type) as type by fields.name | table fields.name, Type, count | rename fields.name as name, count as "Total Count"
| appendcols [search index="The index I am looking for" fields.result="success" | stats count, values(fields.type) as type by fields.name | table fields.name, Type, count | rename fields.name as name, count as "success"] 
| appendcols [search index="The index I am looking for" fields.result="failure" | stats count, values(fields.type) as type by fields.name | table fields.name, Type, count | rename fields.name as name, count as "failure"]
| appendcols [search index="The index I am looking for" fields.result="N.A." | stats count, values(fields.type) as type by fields.name | table fields.name, Type, count | rename fields.name as name, count as "N.A."]

I noticed that for some columns, e.g. column with the heading "failure", do not have their rows aligned with the other rows, resulting in the total count column not matching with all the counts in the rows being added up.

E.g. referencing from the table presented earlier, with the search query I created, the table below is generated:

name      | type       | success | failure | N.A. | Total count
Item A    | fruits     |    5    |   2     |   1  |     6
Item B    | vegetables |    0    |   3     |   3  |     5
Item C    | sweets     |    1    |   0     |   2  |     6

I'd appreciate if advice can be given on how to improve upon the search query, or if possible, to correct me on better suited commands to use.

splunk
1 Answers

As you've discovered, the appendcols command works right under somewhat limited circumstances. The order and count of results from appendcols must be exactly the same as that from the main search and other appendcols commands or they won't "line up".

One solution is to use the append command and then re-group the results using stats.

index=foo 
| stats count, values(fields.type) as Type by fields.name 
| fields fields.name, Type, count 
| rename fields.name as name, count as "Total Count"
| append [search index=foo fields.result="success" 
  | stats count, values(fields.type) as Type by fields.name 
  | fields fields.name, Type, count 
  | rename fields.name as name, count as "success"] 
| append [search index=foo fields.result="failure" 
  | stats count, values(fields.type) as Type by fields.name 
  | fields fields.name, Type, count 
  | rename fields.name as name, count as "failure"]
| append [search index=foo fields.result="N.A." 
  | stats count, values(fields.type) as Type by fields.name 
  | fields fields.name, Type, count 
  | rename fields.name as name, count as "N.A."]
| stats values(*) as * by name
| table fields.name, Type, count

Another solution avoids using append at all.

index=foo 
| rename fields.* as *
| stats count as "Total Count", 
        sum(eval(result="success")) as success,
        sum(eval(result="failure")) as failure, 
        sum(eval(result="N.A.")) as "N.A.", 
        values(type) as Type by name 
| table name, Type, success, failure, "N.A.", "Total Count"

The construct sum(eval(<<expression>>)) counts the results where <<expression>> is true.

user_22274200
Related