How to achieve vlookup in Splunk

[karthikklv]

Hi,

Kindly help me with the search query for my scenario.
I have a lookup table A and a search B with common field user_id. I need to find list of users who are present in lookup A, but not in Search B, over a period of time.

I did write query but it doesn't return any result.

|inputlookup A.csv | fields user_id | search user_id NOT [ search index=X sourcetype=P | fields USER_ID | dedup USER_ID | table USER_ID]

Appreciate your help. Thx.

[gcusello · Accepted solution]

Hi karthikklv,
try something like this

|inputlookup A.csv 
| eval count=0 
| append [ search index=X sourcetype=P | stats count by USER_ID]
| stats sum(count) AS Total by USER_ID
| where Total=0

Users with Total=0 are the ones present in lookup A and not present in search B.

if you're not sure about USER_ID case, you could put an eval to uppercase:

|inputlookup A.csv 
| eval count=0,  USER_ID=upper(USER_ID)
| append [ search index=X sourcetype=P | eval USER_ID=upper(USER_ID) | stats count by USER_ID]
| stats sum(count) AS Total by USER_ID
| where Total=0

Bye.
Giuseppe

[karthikklv]

Thanks for the query. This is what I was exactly looking for.

[hhGA]

Hi,

Can you give this a try:

| inputlookup A.csv
| table USER_ID
| join type=left USER_ID
[ search index=X sourcetype=P
| dedup USER_ID
| eval in_search="Yes"
| table USER_ID , in_search]
| WHERE in_search!="Yes"
| table USER_ID

[karthikklv]

Thank for the query. But, this didn't work for my scenario.