topic Re: How do I construct a table with values being evaluated per number of field values? in Splunk Search

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

giovere — Thu, 23 Oct 2014 14:32:28 GMT

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 ...

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

yannK — Thu, 23 Oct 2014 14:36:47 GMT

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 ?

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

giovere — Thu, 23 Oct 2014 14:52:07 GMT

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.

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

gkanapathy — Thu, 23 Oct 2014 15:11:18 GMT

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?

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

giovere — Thu, 23 Oct 2014 15:18:09 GMT

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.

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

yannK — Thu, 23 Oct 2014 16:44:51 GMT

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

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

giovere — Thu, 23 Oct 2014 18:31:34 GMT

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?

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

yannK — Thu, 23 Oct 2014 19:01:32 GMT

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

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

giovere — Thu, 23 Oct 2014 19:26:52 GMT

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?

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

giovere — Fri, 31 Oct 2014 10:54:35 GMT

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