https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241312#M71754 <P>We have a <STRONG>single source</STRONG> with data (in table form) looking something like this:</P> <TABLE><THEAD> <TR> <TH>Name</TH> <TH>Position</TH> <TH>Department</TH> </TR> </THEAD><TBODY> </TBODY></TABLE> <PRE><CODE>John Whatever 5 Jack Boss 5 Jane Particular 5 </CODE></PRE> <P>Multiple departments. Each department has one Boss.</P> <P>How to write a search to find out Names of bosses of everybody with a position of Particular?</P> Mon, 10 Oct 2016 20:23:15 GMT pm771 2016-10-10T20:23:15Z https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241312#M71754 <P>We have a <STRONG>single source</STRONG> with data (in table form) looking something like this:</P> <TABLE><THEAD> <TR> <TH>Name</TH> <TH>Position</TH> <TH>Department</TH> </TR> </THEAD><TBODY> </TBODY></TABLE> <PRE><CODE>John Whatever 5 Jack Boss 5 Jane Particular 5 </CODE></PRE> <P>Multiple departments. Each department has one Boss.</P> <P>How to write a search to find out Names of bosses of everybody with a position of Particular?</P> Mon, 10 Oct 2016 20:23:15 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241312#M71754 pm771 2016-10-10T20:23:15Z https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241313#M71755 <P>Try this</P> <PRE><CODE>base search | table Name Position Department | eventstats values(eval(if(Position="Boss", Name, null()))) as Boss by Deptartment | where Position="Particular" </CODE></PRE> <P>*<STRONG><EM>OR</EM></STRONG>*</P> <PRE><CODE>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()) </CODE></PRE> Mon, 10 Oct 2016 20:38:59 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241313#M71755 sundareshr 2016-10-10T20:38:59Z https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241314#M71756 <P>I'm confused. Each department has its own Boss. How do you relate Particulars to <STRONG>their</STRONG> Bosses?</P> Mon, 10 Oct 2016 20:55:40 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241314#M71756 pm771 2016-10-10T20:55:40Z https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241315#M71757 <P>I'll breakdown the second query....</P> <PRE><CODE>| eventstats values(eval(if(Position="Boss", Name, null()))) as Boss by Deptartment | eval </CODE></PRE> <P><CODE>eventstats</CODE> command adds summary statistics to all search result. So what this query segment does, is it gets the <CODE>Name</CODE> if <CODE>Position="Boss"</CODE> and assigns it to a field called "Boss" for everyone in the (<CODE>by</CODE>) 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?</P> <PRE><CODE>Boss=if(Position="Particular", Boss, null()) </CODE></PRE> <P>Just nulls out all Boss fields that do not have a corresponding "Position="Particular"</P> <P><A href="https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Eventstats">https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Eventstats</A></P> Mon, 10 Oct 2016 22:11:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241315#M71757 sundareshr 2016-10-10T22:11:38Z https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241316#M71758 <P>Like this:</P> <PRE><CODE>... | eval BOSS=if((Position="Boss"), Name, null()) | stats values(BOSS) AS BOSS values(Position) AS Position BY Department | search Position="Particular" | table BOSS </CODE></PRE> Tue, 11 Oct 2016 18:24:58 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-all-fieldA-values-where-fieldB-is-a-certain-value/m-p/241316#M71758 woodcock 2016-10-11T18:24:58Z