Archive

How to achieve vlookup in Splunk

Engager

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 userid | search userid NOT [ search index=X sourcetype=P | fields USERID | dedup USERID | table USER_ID]

Appreciate your help. Thx.

Tags (1)
0 Karma
1 Solution

Legend

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

View solution in original post

Legend

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

View solution in original post

Engager

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

0 Karma

Communicator

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

0 Karma

Engager

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

0 Karma