How to search all fieldA values where fieldB is a certain value?

[pm771]

We have a single source with data (in table form) looking something like this:

Name	Position	Department

John   Whatever    5
Jack   Boss        5
Jane   Particular  5

Multiple departments. Each department has one Boss.

How to write a search to find out Names of bosses of everybody with a position of Particular?

[woodcock]

Like this:

... | eval BOSS=if((Position="Boss"), Name, null())
| stats values(BOSS) AS BOSS values(Position) AS Position BY Department
| search Position="Particular" | table BOSS

[sundareshr · Accepted solution]

Try this

base search | table Name Position Department | eventstats values(eval(if(Position="Boss", Name, null()))) as Boss by Deptartment | where Position="Particular"

*OR*

base search | table Name Position Department | eventstats values(eval(if(Position="Boss", Name, null()))) as Boss by Deptartment | eval Boss=if(Position="Particular", Boss, null())

[pm771]

I'm confused. Each department has its own Boss. How do you relate Particulars to their Bosses?

[sundareshr]

I'll breakdown the second query....

| eventstats values(eval(if(Position="Boss", Name, null()))) as Boss by Deptartment | eval 

eventstats command adds summary statistics to all search result. So what this query segment does, is it gets the Name if Position="Boss" and assigns it to a field called "Boss" for everyone in the (by) Department. You can check this by removing the last segment. You should see the Department's Boss' name for each person in the Department. Is that not what you see?

Boss=if(Position="Particular", Boss, null())

Just nulls out all Boss fields that do not have a corresponding "Position="Particular"

https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Eventstats