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?
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())
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
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())
I'm confused. Each department has its own Boss. How do you relate Particulars to their Bosses?
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