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.
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
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
Thanks for the query. This is what I was exactly looking for.
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
Thank for the query. But, this didn't work for my scenario.