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