Splunk Search

In a table, how do I get the count for multiple field results?

New Member

Hi All,

I've been trying to figure out for some time how to get the count of the events for each individual fields and get it displayed by the events as the row while the fields stay the same as columns but the result is the count for each event.

I have tried using stats count for each field name but did not get any results.

Please take the work example below to explain.

Work Example: How do i get from the first table on the left to the table on the right as shown?

alt text

Thanks!
zovin

0 Karma

SplunkTrust
SplunkTrust

hi @zovinchong

Can you please try this?

YOUR_SEARCH
| table Ann Betty Charlie Dave 
| streamstats window=1 values(*) as class_* 
| foreach class_* 
    [ eval class=mvappend(class,<<FIELD>>)] 
| eval class=mvdedup(class) | fields - class_* | mvexpand class | eval Ann=if(class=Ann,1,0),Betty=if(class=Betty,1,0),Charlie=if(class=Charlie,1,0),Dave=if(class=Dave,1,0) | stats sum(*) as * by class

Sample Search:

| makeresults 
| eval Ann="a",Betty="a",Charlie="b",Dave="a" 
| append 
    [| makeresults 
    | eval Ann="a",Betty="b",Charlie="b",Dave="b" ] 
| append 
    [| makeresults 
    | eval Ann="a",Betty="c",Charlie="b",Dave="b" ] 
| append 
    [| makeresults 
    | eval Ann="b",Betty="c",Charlie="c",Dave="b" ] 
| append 
    [| makeresults 
    | eval Ann="b",Betty="c",Charlie="c",Dave="c" ] 
| table Ann Betty Charlie Dave 
| streamstats window=1 values(*) as class_* 
| foreach class_* 
    [ eval class=mvappend(class,<<FIELD>>)] 
| eval class=mvdedup(class) | fields - class_* | mvexpand class | eval Ann=if(class=Ann,1,0),Betty=if(class=Betty,1,0),Charlie=if(class=Charlie,1,0),Dave=if(class=Dave,1,0) | stats sum(*) as * by class

Thanks

0 Karma

Champion

Hi,

Try this - to make my work easier I have mapped anne=a1,betty=b1,charlie=c1,dave=d1
| stats count(a1) by a1| appendcols [search |stats count(b1) by b1]|appendcols [search |stats count(c1) by c1] |appendcols [search |stats count(d1) by d1] | fillnull value=NA | fields a1,count(a1),b1,count(b1),c1,count(c1),d1,count(d1)

The output is not exact to what you want, but I wonder if it will suffice

0 Karma

New Member

Hi, sorry I think that is not what I was looking for as this is the output which was displayed after I ran the query.

a1 count(a1) b1 count(b1) c1 count(c1) d1 count(d1)
a 3

b 2

c 0

0 Karma

Champion

Hi,
Couple of things here -
If you look at the output that you are receiving now, the counts are correct for a1(anne), anne has as field values of 3 a's and 2 b's as per your work sample.
Now as to the rest of the column names, you have to give your base search before the stats in each search query , like this -yourbasesearch| stats count(a1) by a1| appendcols [search yourbasesearch |stats count(b1) by b1]|appendcols [search yourbasesearch |stats count(c1) by c1] |appendcols [search yourbasesearch|stats count(d1) by d1] | fillnull value=NA | fields a1,count(a1),b1,count(b1),c1,count(c1),d1,count(d1)

each appendcols also needs the base search,your index or whatever the search is that you are using.
Sorry I had not noticed that appendcols was missing the searches

0 Karma

New Member

Hi,

This time I've managed to get results filled for the other columns.
However, I did not obtained in the desired row formatting as what I wanted in the work example.

What i obtained this time was:

a1 count(a1) b1 count(b1) c1 count(c1) d1 count(d1)
a 3 a 1 b 3 a 1
b 2 b 2 c 2 b 3
NA NA c 3 NA NA c 1

Perhaps I need to create a new field with the values a,b,c inside?

0 Karma

Champion

As I said in my initial answer, the output is not exact to what you want .
Part of the problem with creating a,b,c inside is that the values for a1,b1,c1,d1 will be dynamic (i think?).So if you hard code the field values what happens for instance when a1 has a field value of say 'd'?
What you are really asking for can be achieved through datamodel and pivot command,if you look at it closely , in fact what you want is a sort of pivot, see this - https://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Datamodel
and this
http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Pivot
But is it worthwhile to build a datamodel and pivot just for this ? The search query we have right now gives us the counts and will work for any field values that a1,b1.c1 or d1 might have..think about it

0 Karma

Motivator

I'm naming your a,b,c as class.

basesearch | chart count by class name
0 Karma

New Member

Hi, may i know how do you name a,b,c as a field called 'class'?

0 Karma

Motivator

your a,b,c must be coming from somewhere. what is the field name that holds that data? what does your event look like?

0 Karma