How to join two queries with a common field?

[Shashank_87]

Hi, I have 2 searches which i need to join using a common field let's say uniqueId. Now in my 1st search I have a username and in the 2nd search I see if the user goes through that request.
I want to display the user names who does not triggers any request in the 2nd search.
Please note both are different indexes. I am just looking for which user names the request has not been triggered.
I have used join and combined the queries where I am getting the common results but I want to display the uncommon results.

[yannK]

an efficient way is to do a search looking at both indexes, and look for the events with the same values for uniqueId

 uniqueId=* (index=index1 OR index=index2) 
 | stats dc(index) AS distinctindexes values(index) values(username) AS username  by uniqueId 
 | where distinctindexes>1

[renjith_nair · Accepted solution]

Hi @Shashank_87,

You could do it by using NOT in subsearch

index=index1 source=xxx  |search NOT [index=index2 "other search terms" |fields user_field]

https://docs.splunk.com/Documentation/Splunk/7.1.1/Search/Usesubsearchtocorrelateevents

[Shashank_87]

This doesn't work. I have a common field with same values but different name in the 2 different indexes. I am able to group them together. But what I am really looking for is the username which is in 1st index does not have any event in 2nd index. I want a table or something for those users.
This is my current search which gives me the common events. I want the uncommon one's -

index=test1 "any search string"
| join uniqueId
[ search index=test2 "any search string"
| rename CustomerId as uniqueId]
| table username uniqueId

[renjith_nair]

What are you getting when you execute with NOT, because NOT will remove the events which have "user" in second index