Splunk Search

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

giovere
Path Finder

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

Tags (3)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

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

View solution in original post

giovere
Path Finder

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

0 Karma

yannK
Splunk Employee
Splunk Employee

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
Path Finder

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?

0 Karma

yannK
Splunk Employee
Splunk Employee

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

0 Karma

giovere
Path Finder

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?

0 Karma

giovere
Path Finder

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.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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?

0 Karma

giovere
Path Finder

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.

0 Karma

yannK
Splunk Employee
Splunk Employee

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 ?

0 Karma
Get Updates on the Splunk Community!

Edge Processor | New Resiliency Improvements & Support for Additional Data Sources

We are excited to announce several exciting updates for Edge Processor aimed at hardening overall product ...

Splunk Certification Support Alert | Pearson VUE Outage

Splunk Certification holders and candidates!  Please be advised of an upcoming system maintenance period for ...

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...