How to use the Where clause in my search?

[vnguyen46]

I have index A with fields: username, field1, field2
I have main:sourcetype B with fields: userid, fullname

Trying to search for: username, field1, field2 WHERE username = userid
Any ideas will help.
Thanks,

[aholzer · Accepted solution]

you can use a subsearch to limit the results to those that have a match from your secondary information source.

index=A [| search index=main sourcetype=B | fields userid | rename userid AS username]

This will return only results from index=A where the username is in the list of userid's from index=main sourcetype=B.

Warning: subsearches have a 10k limit in terms of results that can be returned, so if you have more than 10k results in your secondary information source this will not work

Hope this helps

[vnguyen46]

It works well. My index main <2000 records.
Thank you.

[vnguyen46]

The key is "userid" in a different index/sourcetype. I meant looking for records in index A for only userid in index/sourcetype B.
index=A OR sourcetype=B
...somewhere I need to add A.username=B.userid
that means only userid in soucetype B will display

[burwell]

If you just want to search for a field having a value in the first part of the search leave off the WHERE