how to compare values from two different searches

[a212830]

Hi,

I need to run a compare against the count of two different searches - how would I do that? I'm counting the number of unique sources from two different indexes, and they need to be the same.

[MuS]

Hi a212830,

Read https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... and watch the March 2016 virtual.conf talk from http://wiki.splunk.com/Virtual_.conf for more info how this can be done.

Hope this helps ...

cheers, MuS

[jkat54]

I like the elegance of using OR and I will have to revisit some old searches.

Would this work for the op's scenario where they want a distinct count of events in two different indexes?

[MuS]

of course:

index=_internal OR index=_audit 
| eval internal_count=if(index="_internal", 1, null()) 
| eval audit_count=if(index="_audit", 1, null()) 
| stats sum(internal_count) AS internal sum(audit_count) AS audit 
| eval diff=internal-audit

[wrangler2x]

How would you do that for comparing the count of two sourcetypes in one index?

[wrangler2x]

Ah, got that figured out. And using your technique you can do it across two as well. Cool!

[jkat54]

That's so awesome! TYVM!!!

[chimell]

Hi
Use this search code and look at the difference in the results

      index=_internal | stats dc(source) AS C1| appendcols [search index=_audit| stats dc(source) AS C2 ] |table C1 C2

[jplumsdaine22]

A simple stats command should do the trick

index=index1 OR index=index2 | stats dc(source) by index

Have a read http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Stats

If theres one command you learn, make it stats!

[somesoni2]

It depends upon what type of searches and what columns are available on those two searches. Could you provide some more information on the output of the those two searches? Based on that it could be appendcols OR join OR may be simple stats can do the job.

[jkat54]

  index=index1 | dedup source | stats dc(source) AS idx1ct | appendcols [search index=index2 | dedup source | stats dc(source) AS idx2ct ] | eval nodiff=if(match(idxct1,idxct2),"True","False") | table nodiff

[somesoni2]

I guess you should be using appendcols here. Append will create those fields in totally different events/rows and your eval will fail.

[jkat54]

changed to appendcols, thanks. So a little more explanation now that I'm not on my phone. The search creates a field called nodiff that is true if there isnt a difference in the count of sources between indexes, or false if there is a difference. The dedups speed up the stats distinct count functions but are not required. Remove the final table to see the rest of the fields.

[a212830]

Thanks!!!!

[jkat54]

If this answered your question can you mark it as the answer please?

Also see MuS's answer below. Apparently it is more efficient than appendcols. If you find that the search is faster or more reliable using his technique, then please mark his as the answer.