How to combine two searches into one and display a table with the results of search1, search2, and the difference between both results?

[splunked38]

Hi,

I've got two distinct searches producing tables for each, and I'd like to know if I can combine the two in one table and get a difference between the two.

Country is the same value in both tables

I tried:

sourcetype=a...  | stats count(Name) by Country | append [sourcetype=b.... | stats count(Name) by Country] | delta count(Name) as diff

eg:
search1: sourcetype=... | stats count(Name) by Country

search 2: sourcetype=.... | stats count(Name) by Country

Table:

Country    search1   search2   difference
-----------------------------------------------------------
Country1   result1   result2   result1-result2 
Country2   result1   result2   result1-result2 
.
.

Thanks in advance

[acharlieh · Accepted solution]

How about:

| multisearch [search sourcetype=a... | eval type="search1"] [sourcetype=b.... | eval type="search2"]
| chart count(Name) over Country by type
| eval difference = search1-search2

multisearch runs both searches, and we apply a label to each, and returns all results. (If there's a natural label in the data we could use, such as sourcetype, we could skip the multisearch, and just use an ORed search). We use chart to build the base of your desired table, and then eval to calculate the difference.

[splunked38]

Perfect, that works. Thanks.

Question: when you state 'natural label' we have the same source type and host but different rex statements after that.

How would the ORed search be applied?
ie:
search sourcetype=a host=a.com | rex a...
search sourcetype=a host=a.com | rex b...

(there is some optimisation required to move the rex statements as fields)

The original example had two different sourcetypes as I have another situation where the searches are completely different.

Side note: the original searches had 'stats' statements that had to be removed when querying. No big deal but it may help someone in the future.

[acharlieh]

I was meaning a field instead of our artificially created "type" | multisearch [search sourcetype=a host=foo] [search sourcetype=b host=bar] could also be written as (sourcetype=a host=foo) OR (sourcetype=b host=bar)

I would have to know more about the searches and the data to know for certain but assuming rex a and rex b are extracting different fields (a and b respectively) one option could be to combine them like so (off top of my head so syntax might be slightly off), but knowing more about your searches and data could lead you and others to find better optimizations.
sourcetype=a host=a.com | rex a... | rex b... | eval type = split(case(isnotnull(a) AND isnotnull(b),"a b",isnotnull(a),"a", isnotnull(b),"b")," ") | chart ... | eval difference ...

[splunked38]

Thanks again.

I'll take a shot at optimising this and will create a new question if required.