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!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...