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
Communicator

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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...