Splunk Search

Why does the Join query only work if I write a query in the most inefficient way?

paulalbert
Engager

I am trying to join the results of two searches so it looks like this:

CWID, authorization_pk,weillCornellEduPrimaryTitle
paa2013, 1234, IT Support Manager
paa2013, 5678, IT Support Manager
paa2013, 9123, IT Support Manager

**

The "AcctAdmin" search returns only 3 results...

CWID, authorization_pk
paa2013, 1234
paa2013, 5678
paa2013, 9123


The "LDAP" search returns 150,000 results...

CWID, weillCornellEduPrimaryTitle
paa2013, IT Support Manager
brb2009, Professor
ala9001, Help Desk Technician
[150,000 more results!]

**

Search #1, in which I start with the smaller result and join against the larger result. I presume this would be more efficient, but it doesn't work:

| dbquery "AcctAdmin" "select CWID, authorization_pk from database.authorization a  
left join people_db.service s on s.service_pk = a.service_fk  
left join people_db.person p on p.PERSON_PK = a.PERSON_FK 
where CWID ='paa2013'"  
| rename CWID as weillCornellEduCWID
| join weillCornellEduCWID [
| ldapsearch domain=ED-people search="objectClass=eduPerson" attrs="weillCornellEduCWID,weillCornellEduPrimaryTitle"
| table weillCornellEduCWID, weillCornellEduPrimaryTitle
]
| table *

Search #2, in which I start with the larger result and join against the smaller result, does:

| ldapsearch domain=ED-people search="objectClass=eduPerson" attrs="weillCornellEduCWID,weillCornellEduPrimaryTitle"
| table weillCornellEduCWID, weillCornellEduPrimaryTitle
| rename weillCornellEduCWID AS CWID
| join CWID [
| dbquery "AcctAdmin" "select CWID, authorization_pk from people_db.database a  
left join people_db.service s on s.service_pk = a.service_fk  
left join people_db.person p on p.PERSON_PK = a.PERSON_FK 
where CWID ='paa2013'"  ]
| table *

A lot of answers here suggest JOIN is inefficient but I don't see another way to do this. Oh, and "max=0" is no help.

Tags (2)
0 Karma

sashraf
New Member

I would stick with what elliot is trying to do but seeing as though you have already hard coded a where clause in your DB query to limit your results, you should do the same in your LDAP query too so that it looks something like this:

| ldapsearch domain=ED-people search="(&(objectClass=eduPerson)(weillCornellEduCWID=paa2013))" attrs="weillCornellEduCWID,weillCornellEduPrimaryTitle"
 | table weillCornellEduCWID, weillCornellEduPrimaryTitle
 | rename weillCornellEduCWID AS CWID
 | append [
 | dbquery "AcctAdmin" "select CWID, authorization_pk from people_db.database a  
 left join people_db.service s on s.service_pk = a.service_fk  
 left join people_db.person p on p.PERSON_PK = a.PERSON_FK 
 where CWID ='paa2013'"  ]
0 Karma

paulalbert
Engager

This works but I do need to also display weillCornellEduPrimaryTitle? That's the point of having these two searches.

My query

0 Karma

Richfez
SplunkTrust
SplunkTrust

Another exploratory thought:

Is there any reason you can't create a lookup from the LDAP search? That seems like the most efficient solution to this.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

What does each search give you and what are the expected results?

---
If this reply helps you, Karma would be appreciated.
0 Karma

paulalbert
Engager

Thanks for having a look, Rich. See above.

0 Karma

elliotproebstel
Champion

Any chance this code does what you're looking for?

| ldapsearch domain=ED-people search="objectClass=eduPerson" attrs="weillCornellEduCWID,weillCornellEduPrimaryTitle"
| table weillCornellEduCWID, weillCornellEduPrimaryTitle
| rename weillCornellEduCWID AS CWID
| append [
| dbquery "AcctAdmin" "select CWID, authorization_pk from people_db.authorization a  
 left join people_db.service s on s.service_pk = a.service_fk  
 left join people_db.person p on p.PERSON_PK = a.PERSON_FK 
 where CWID ='paa2013'"  ]
| stats values(*) AS * BY CWID
0 Karma

paulalbert
Engager

Thanks for taking a whack but not really. This returns 150k results! 🙂

0 Karma

elliotproebstel
Champion

Based on the updated info, how about this:

| ldapsearch domain=ED-people search="objectClass=eduPerson" attrs="weillCornellEduCWID,weillCornellEduPrimaryTitle"
| table weillCornellEduCWID, weillCornellEduPrimaryTitle
| rename weillCornellEduCWID AS CWID
| append [
 | dbquery "AcctAdmin" "select CWID, authorization_pk from people_db.authorization a  
  left join people_db.service s on s.service_pk = a.service_fk  
  left join people_db.person p on p.PERSON_PK = a.PERSON_FK 
  where CWID ='paa2013'"  ]
| stats list(CWID) AS CWID list(weillCornellEduPrimaryTitle) AS weillCornellEduPrimaryTitle BY authorization_pk

That should bring you down to three rows again 🙂

0 Karma

paulalbert
Engager

I see what you're trying to do but unfortunately, it gives me zero results. 🙂

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...