Splunk Search

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

zovinchong
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

kamlesh_vaghela
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

Sukisen1981
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

zovinchong
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

Sukisen1981
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

zovinchong
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

Sukisen1981
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

kmaron
Motivator

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

basesearch | chart count by class name
0 Karma

zovinchong
New Member

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

0 Karma

kmaron
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...