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

[janderson19]

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,

[mraudaschl]

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

[thambisetty · Accepted solution]

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)

[thambisetty]

@janderson19 

👍 is really appreciated 😁

[to4kawa]

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

[to4kawa]

see the command reference and use AS clause.

lookup can use different field names.

[janderson19]

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.