How do you search two indexes with disparately named fields for instances where data is in one instance but not the other?

[chirsf]

Hi,

First time asking. I did a search, but maybe I used the wrong keywords. Apologies if this is a duplicate.

I have two indexes that I want to compare two disparately named fields in. The data is IP addresses, so the string should be the same format across both.

In SQL, I would do something like "where not in foo" or whatever. But, I have yet to figure out how to do a compare and then look for anything only in one index or only in the other index.

Any tips would be appreciated. Thanks

[nplamondon]

Some sample data as well as your expected results would go a long way toward helping answer your question.

[chirsf]

Here's kind of what I had been working on, which is based on what @whrg posted.

index=INDEX1 LocalAddressIP4!=127.0.0.1 LocalAddress!=0.0.0.0
NOT [search index=INDEX2 | rename ip as LocalAddressIP4 | fields LocalAddressIP4]
| table LocalAddressIP4

My expected results is a table of ip addresses which are in INDEX1 but not INDEX2. I don't think I can give out sample data from what I have.

[whrg]

@chirsf
You wrote "LocalAddressIP4!=127.0.0.1 LocalAddress!=0.0.0.0". Is that a typo or do you have multiple IP fields (LocalAddressIP4 + LocalAddress) in INDEX1?
Because if you search for nonexistentfield!="something" then you won't get any results.

[chirsf]

Ugh that's a typo yea. That's what I get for transposing and not copying the text.

[whrg · Accepted solution]

Hi!
Try using a subsearch like this:

index=index1 NOT [search index=index2 | fields ip_field]

In this example, both data sets should have the field ip_field.

If the field names (which you want to compare) are different, use rename to make them the same:

index=index1 NOT [search index=index2 | rename device as ip_field | fields ip_field]

[whrg]

For testing purposes, I uploaded the following csv file into index1:
time,ip4
2018-12-04,192.168.0.1
2018-12-04,192.168.0.2
2018-12-04,192.168.0.3
2018-12-04,192.168.0.4

Then I uploaded this file into index2:
time,ip4
2018-12-04,192.168.0.2
2018-12-04,192.168.0.4
2018-12-04,192.168.0.5

When I search for "index=index1 | table ip4" then I get:
ipv4
192.168.0.4
192.168.0.3
192.168.0.2
192.168.0.1

When I search for "index=index2 | table ip4" then I get:
192.168.0.5
192.168.0.4
192.168.0.2

Now when I search for "index=index1 NOT [search index=index2 | fields ip4] | table ip4" then I get (as expected):
192.168.0.3
192.168.0.1

[chirsf]

Thank you this does exactly what I was looking for.