https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325883#M97150 <P>Funny, I don’t remember helping much! Happy to know even if I had a small part in creating a splunk monster like you though @daljeanis.</P> Fri, 08 Dec 2017 23:05:22 GMT jkat54 2017-12-08T23:05:22Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325869#M97136 <P>I have combined data from two searches and want to compare them to identify what is new in the second search, what is removed from the first, and what is persistent across both searches. My data looks like:<BR /> asset event search<BR /> 1 a 1st<BR /> 1 a 2nd<BR /> 1 b 1st<BR /> 1 c 2nd<BR /> I want the results to look like<BR /> asset event status<BR /> 1 a persistent<BR /> 1 b removed<BR /> 1 c new</P> <P>How would I go about doing this? Im thinking a combination of eval with nested if statements, but really not sure if this is the best approach or how to execute. </P> Fri, 08 Dec 2017 16:00:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325869#M97136 glenngermiathen 2017-12-08T16:00:06Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325870#M97137 <P>Can you post your searches?</P> Fri, 08 Dec 2017 16:03:49 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325870#M97137 jplumsdaine22 2017-12-08T16:03:49Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325871#M97138 <P>Try like this(<STRONG>fixed typo on mvcount</STRONG>)</P> <PRE><CODE>your current search with results from both searches | table asset event search | stats values(search) as search by asset event | eval status=case(mvcount(search)=2,"persistent",search="1st","removed",true(),"new") | table asset event status </CODE></PRE> Fri, 08 Dec 2017 16:18:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325871#M97138 somesoni2 2017-12-08T16:18:12Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325872#M97139 <P>My high-level approach to this would be:</P> <PRE><CODE>first search | eval from_first=1 | append [ second search | eval from_second=1 ] | stats values(from_first) AS from_first, values(from_second) AS from_second BY asset event | eval status=case(from_first=1 AND from_second=1, "persistent", from_first=1 AND isnull(from_second), "removed", isnull(from_first) AND from_second=1, "new") | fields - from_first from_second </CODE></PRE> <P>There may well be a more efficient way to do it if we analyze your specific search queries. </P> Fri, 08 Dec 2017 16:22:34 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325872#M97139 elliotproebstel 2017-12-08T16:22:34Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325873#M97140 <P>Works great, thanks! Just a typo on mvcount</P> Fri, 08 Dec 2017 17:19:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325873#M97140 glenngermiathen 2017-12-08T17:19:44Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325874#M97141 <P>Oops. Fixed.</P> Fri, 08 Dec 2017 17:21:59 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325874#M97141 somesoni2 2017-12-08T17:21:59Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325875#M97142 <P>@elliotproebstel - That would work... and I'll give you some general comments about improving efficiency, since you brought it up. <span class="lia-unicode-emoji" title=":winking_face:">😉</span></P> <P>In general, you never want to have two searches when one will do. So this pseudocode...</P> <PRE><CODE> first search | eval from_first=1 | append [ second search | eval from_second=1 ] </CODE></PRE> <P>...should be replaced whenever possible by this pseudocode... </P> <PRE><CODE> (first search) OR (second search) | eval from_first=case( this is from the first search, 1) | eval from_second=case( this is from the second search, 1) </CODE></PRE> <P>To make searches as efficient as possible, one should always get in the habit of getting rid of all unneeded fields. There aren't any in the example data, but just for good practice, there should be a <CODE>fields</CODE> command there. </P> <P>There is only going to be a single value or none, so <CODE>values()</CODE> and <CODE>max()</CODE> are equivalent. I tend to use <CODE>max()</CODE> in that case, because it implicitly guarantees there will not be multiple values, and you don't ever have to look higher in the code to confirm.</P> <PRE><CODE> (first search) OR (second search) | fields asset event ... plus whatever I need to differentiate between searches | eval from_first=case( this is from the first search, 1) | eval from_second=case( this is from the second search, 1) | stats max(from_first) as from_first max(from_second) as from_second by asset event </CODE></PRE> <P>Technically, you don't have to create the from_* fields there, and since you only want to test existence or not, so you can use a <CODE>count(eval())</CODE> in the stats instead of creating fields.</P> <PRE><CODE> (first search) OR (second search) | fields asset event ... plus whatever I need to differentiate between searches | stats max(eval(test that produces a 1 if first search or null otherwise))) as found_first, max(eval(test that produces a 1 if second search or null otherwise))) as found_second by asset event </CODE></PRE> Fri, 08 Dec 2017 19:00:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325875#M97142 DalJeanis 2017-12-08T19:00:18Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325876#M97143 <P>Thank you! As usual, @DalJeanis, I learn so much from your feedback. I really appreciate you taking the time to share this.</P> Fri, 08 Dec 2017 19:11:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325876#M97143 elliotproebstel 2017-12-08T19:11:36Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325877#M97144 <P>@elliotproebstel - Well, I notice you and @kamlesh_vaghela spending a lot of time helping people out, so I decided to invest some time in y'all Dalsplaining whenever I can. @woodcock and @somesoni2 and @jkat54 and many others did that for me, so I'm passing it on, and I'm sure you will too.</P> <P>By the way, get on the Splunk slack channel if you aren't already. Plenty more good stuff happening there.</P> Fri, 08 Dec 2017 19:31:55 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325877#M97144 DalJeanis 2017-12-08T19:31:55Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325878#M97145 <P>Thanks. I did send in a request for the Splunk slack channel a few days ago, so hopefully I'll get approved soon!</P> Fri, 08 Dec 2017 19:41:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325878#M97145 elliotproebstel 2017-12-08T19:41:16Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325879#M97146 <P>Hmmm. Okay, @ellliotproebstel tag me sometime this weekend if it hasn't gotten done, and I'll rattle some cages. I thought that was mostly automatic. </P> <P>@lfedak, any idea who needs to be pinged to allow this extremely helpful person into the slack fold?</P> Fri, 08 Dec 2017 19:47:34 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325879#M97146 DalJeanis 2017-12-08T19:47:34Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325880#M97147 <P>"Dalsplaining" -- that is great.<BR /> @elliotproebstel, we love your participation on Answers! On it and will update if there's any roadblack to approval for Slack.</P> Fri, 08 Dec 2017 19:50:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325880#M97147 lfedak_splunk 2017-12-08T19:50:36Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325881#M97148 <P>^^ See comment above</P> Fri, 08 Dec 2017 19:50:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325881#M97148 lfedak_splunk 2017-12-08T19:50:54Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325882#M97149 <P>Thanks, @lfedak. I appreciate it!</P> Fri, 08 Dec 2017 19:53:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325882#M97149 elliotproebstel 2017-12-08T19:53:07Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325883#M97150 <P>Funny, I don’t remember helping much! Happy to know even if I had a small part in creating a splunk monster like you though @daljeanis.</P> Fri, 08 Dec 2017 23:05:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325883#M97150 jkat54 2017-12-08T23:05:22Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325884#M97151 <P>Went the route of the other answer, but this makes good sense too.</P> Sat, 09 Dec 2017 04:07:55 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-two-searches-and-count-multiple-fields-and-values/m-p/325884#M97151 glenngermiathen 2017-12-09T04:07:55Z