Splunk Search

How to find values in a lookup table that do not appear in an index?

janderson19
Path Finder

Hello,

I have a lookup table that I've exported from another report using the fields IP_ADDRESS, CountOfUserID.

I'm trying to find IP Addresses in another index, msad, using primarily the fields ClientIP and UserId, which do not appear in the lookup table.

So, if IP_ADDRESS and ClientIP match, throw the data out, and return a list of the leftover IP_ADDRESS values.

I'm running into issues, where either the search will return the opposite of what I want (IP Addresses that appear in both datasets), or nothing at all.

Does anyone know how to work the logic on this? I feel like I've tried everything.

Thanks,

Labels (2)
0 Karma
1 Solution

thambisetty
Super Champion

index=msad

| stats count  by ClientIP,UserId

| append [| inputlookup yourlookupname | stats c as l_count by IP_ADRESS | rename IP_ADDRESS as ClientIP]
| stats values(*) as * by ClientIP

| where isnull(count)

————————————
If this helps, give a like below.

View solution in original post

0 Karma

mraudaschl
Loves-to-Learn

hi Janderson19, 
I am trying to achieve the same thing but didn't have any luck so far. Were you successfull?

 

0 Karma

thambisetty
Super Champion

index=msad

| stats count  by ClientIP,UserId

| append [| inputlookup yourlookupname | stats c as l_count by IP_ADRESS | rename IP_ADDRESS as ClientIP]
| stats values(*) as * by ClientIP

| where isnull(count)

————————————
If this helps, give a like below.

View solution in original post

0 Karma

thambisetty
Super Champion

@janderson19 

👍 is really appreciated 😁

————————————
If this helps, give a like below.
0 Karma

to4kawa
SplunkTrust
SplunkTrust

index=msad | stats count  by ClientIP,UserId | lookup yourlookup IP_ADDRESS AS ClientIP OUTPUT CountOfUserID | where isnull(CountOfUserID)

 

 

0 Karma

to4kawa
SplunkTrust
SplunkTrust

see the command reference and use AS clause.

lookup can use different field names.

0 Karma

janderson19
Path Finder

Yeah. I got it to match the two datasets pretty easily, but what I'm having trouble with is finding events that *don't* appear in the index.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!