Splunk Search

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

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.

Tags (3)
1 Solution
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

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

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

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?

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

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?

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.

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?

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.

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 ?

Get Updates on the Splunk Community!

#### How to Get Started with Splunk Data Management Pipeline Builders (Edge Processor & ...

If you want to gain full control over your growing data volumes, check out Splunk’s Data Management pipeline ...

#### Out of the Box to Up And Running - Streamlined Observability for Your Cloud ...

Tech Talk Streamlined Observability for Your Cloud Environment Register    Out of the Box to Up And Running ...

#### Splunk Smartness with Brandon Sternfield | Episode 3

Hello and welcome to another episode of "Splunk Smartness," the interview series where we explore the power of ...