How do I construct a table with values being evaluated per number of field values?

[giovere]

I'm not sure I've used the correct terminolgy to ask a question, so I'll jump into example:

input:

Name,beers
Bob,6
Anna,7
Bob,4
Anna,3
Anna,9
Bob,10
Bob,11

I want to count how many times Bob was below 5 and count how many times Anna was below 8. In this case, desired output looks like this:

   Name,Below,Above
   Anna,2,1
   Bob,1,3

In my struggles came up with this:

search ... | eval Desc=case(beers<=5, "Below", beers>5, "Above")  | chart count over Name by Desc

This would have worked, if I had one criteria, but each of the names have its own.
Thanks in advance ...

[giovere]

Probably not the best solution if you have a lot of names, but works Ok with small number of names and Splunk 4.3.*, otherwise with Splunk6 yannK's solution should work fine.

search ... | eval desc=if(match(name,"Bob"),case(beers<=5,"Below",beers>5,"Above"),if(match(name,"Anna"),case(beers<=8,"Below",beers>8,"Above"),"New name")) | stats count(eval(desc="Below")) AS "Below", count(eval(desc="Above")) AS Above by name


[yannK · Accepted solution]

Your search looks ok then,it will update the Desc field for each time the condition is met, and you do the chart count of it

try this

search ... | eval Desc=case(beers<=5, "Below", beers>5, "Above", 1=1,"other") | stats count by Name Desc

search ... | eval Desc=case(beers<=5, "Below", beers>5, "Above", 1=1,"other") | chart count by Name Desc

An alternate method is :

search ... | eval Above=if(beers>5,1,0) | eval Below=if(beers<=5,1,0)| stats sum(Above) AS times_Above sum(Below) AS times_Below by Name

[giovere]

Thank you yannK, I'll give it a try tomorrow, but so far I do not understand where would you define condition for Anna and other names?

[yannK]

I see, you want the conditions to be different PER NAME
by example Anna is above after 1 beer, but Bob after 5.

You need a lookup with those limits in it, one line per name and one columns with the limit value.
call it as a lookup to add the detail to each event, then add the eval conditions after using those files instead of static values.

see lookups. they are simple csv files to upload.
http://docs.splunk.com/Documentation/Splunk/6.1.4/SearchReference/Lookup

example of lookup :
Name,limit
Bob,5
Anna,1

Then after defining it call it
search ... | lookup mylookup Name | eval Desc=case(beers<=limit, "Below", beers>limit, "Above", 1=1,"other") | stats count by Name Desc

[giovere]

That sounds like a solution indeed, but I got stuck with Splunk 4.3.6 at least till the end of a year, any idea if it can be done without lookup? Maybe hard-coded evals per name?

[giovere]

Comparision values (thresholds) are hard-coded, have to be passed through a query. I agree it is sort of ugly, but I do not have any other way to get them in Splunk.

[gkanapathy]

You say each name has its own comparison value, but where does that come from? It's hard to know how to construct a search without knowing where the input comes from?

[giovere]

Column names are: Name and beers.

I'd like first to conut how many entries with Bob have beers >5 (and <=5) and populate table with that count per name.

[yannK]

Can you clarify what are the columns names.
In your example we also see that Bob appears several time.
Bob,6
Bob,4
Bob,10
Bob,11

do you want sum all the numbers first ?
user=Bob count=31

then compare count>5 or <8 ?