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?
Thanks!
zovin
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
Hi,
Try this - to make my work easier I have mapped anne=a1,betty=b1,charlie=c1,dave=d1
The output is not exact to what you want, but I wonder if it will suffice
a1 count(a1) b1 count(b1) c1 count(c1) d1 count(d1)
a 3
b 2
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
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?
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
I'm naming your a,b,c as class.
basesearch | chart count by class name
Hi, may i know how do you name a,b,c as a field called 'class'?
your a,b,c must be coming from somewhere. what is the field name that holds that data? what does your event look like?