Splunk Search

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

chirsf
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
1 Solution

whrg
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

nplamondon
SplunkTrust
SplunkTrust

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

0 Karma

chirsf
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

whrg
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

chirsf
Explorer

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

0 Karma

whrg
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]
0 Karma

whrg
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

chirsf
Explorer

Thank you this does exactly what I was looking for.

Get Updates on the Splunk Community!

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! 🌈 In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...