Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Engager

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

0 Karma
Highlighted

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

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
Highlighted

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

Engager

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

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

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

Engager

Thanks for having a look, Rich. See above.

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Engager

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

My query

0 Karma