Splunk Search
Highlighted

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

Path Finder

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?

Tags (4)
1 Solution
Highlighted

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

Legend

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())
``````
Highlighted

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

Path Finder

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

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

Legend

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

Highlighted

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

Esteemed Legend

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