Splunk Search
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Motivator

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]

View solution in original post

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Explorer

Thank you this does exactly what I was looking for.

Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Motivator

@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.

0 Karma
Highlighted

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

Explorer

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

0 Karma