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

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

paulalbert — Fri, 09 Feb 2018 15:29:23 GMT

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.

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

elliotproebstel — Fri, 09 Feb 2018 17:51:00 GMT

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

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

richgalloway — Fri, 09 Feb 2018 19:40:07 GMT

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

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

paulalbert — Fri, 09 Feb 2018 21:39:15 GMT

Thanks for having a look, Rich. See above.

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

paulalbert — Fri, 09 Feb 2018 21:40:28 GMT

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

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

elliotproebstel — Fri, 09 Feb 2018 21:50:03 GMT

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 🙂

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

paulalbert — Fri, 09 Feb 2018 22:57:06 GMT

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

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

Richfez — Sun, 11 Feb 2018 15:14:51 GMT

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.

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

sashraf — Sun, 11 Feb 2018 15:22:43 GMT

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'"  ]

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

paulalbert — Tue, 13 Feb 2018 14:32:32 GMT

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