Splunk Search

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

pm771
Communicator

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?

0 Karma
1 Solution

sundareshr
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())

View solution in original post

woodcock
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
0 Karma

sundareshr
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())

pm771
Communicator

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

0 Karma

sundareshr
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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...