Splunk Search

How to join two queries with a common field?

Shashank_87
Explorer

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.

0 Karma
1 Solution

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

0 Karma

yannK
Splunk Employee
Splunk Employee

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
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

Shashank_87
Explorer

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

0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...