How to join REST & search results?

[manderson7]

I'm really bad when it comes to join searches, though I've been doing this for years. 

I'm able to find the list of orphaned searches using:

| rest /servicesNS/-/-/admin/directory count=0 splunk_server=<splunkserver>
| rename eai:* as *, acl.* as * 
| eval updated=strptime(updated,"%Y-%m-%dT%H:%M:%S%Z"), updated=if(isnull(updated),"Never",strftime(updated,"%d %b %Y")) 
| sort type 
| eval sAMAccountName=owner 
| stats count by title orphaned sAMAccountName sharing type owner updated app disabled 
| search orphaned=1

and we have a summary index containing our LDAP users & managers for those users. Using the following search returns users and their managers:

index=metrics_summary source="LDAP*" source IN("LDAP GROUP USER DIVISION Summary Index Search" "LDAP_GROUP_USER_DIVISION_Summary_Index_Search" lookup_ldap_group_user_division) sAMAccountName=e* OR sAMAccountName=v* |table sAMAccountName displayName mail department division manager

But I haven't been able to join the two searches together to give me the manager name of the user w/ the orphan search. I've tried variations of the following:

| rest /servicesNS/-/-/admin/directory count=0 splunk_server=<splunkserver> 
| rename eai:* as *, acl.* as * 
| eval updated=strptime(updated,"%Y-%m-%dT%H:%M:%S%Z"), updated=if(isnull(updated),"Never",strftime(updated,"%d %b %Y")) 
| sort type 
| eval sAMAccountName=owner 
| stats count by title orphaned sAMAccountName sharing type owner updated app disabled 
| search orphaned=1
| join sAMAccountName type=outer max=0 
    [|search index=metrics_summary source="LDAP*" source IN("LDAP GROUP USER DIVISION Summary Index Search" "LDAP_GROUP_USER_DIVISION_Summary_Index_Search" lookup_ldap_group_user_division) 
| stats latest(_time) AS latest values(displayName) values(mail) values(distinguishedName) values(department) values(division) latest(userAccountControl) values(manager) by sAMAccountName 
| rename values(*) AS *, latest(*) AS *]

but this only comes back w/ results from the rest call. 

I know I get results using the summary index search. How do I merge these?

Thanks

[jdunlea · Accepted solution]

How many results are you getting with the summary search? If it is more than 10k, then the subsearch will be limited to 10k results (if I remember correctly) and therefore you may be trimming the result set before doing the join. 

If you have more than 10k results from your summary index search, I recommend trying to swap the positions of the REST search and the summary search. So run the summary search first and do the REST search as the subsearch.

Also, you could try converting your sAMAccountName to lowercase in both searches to avoid case issues. Additionally, I recommend doing some sort of "stats .... by sAMAccountName" in your summary search in order to remove any possible duplicates which might be interfering with the join. 

[manderson7]

That helped quite a bit, thanks. The somewhat final search came out to be:

| rest /servicesNS/-/-/admin/directory count=0 splunk_server=<splunkserver> 
| rename eai:* as *, acl.* as * 
| eval updated=strptime(updated,"%Y-%m-%dT%H:%M:%S%Z"), updated=if(isnull(updated),"Never",strftime(updated,"%d %b %Y")) 
| sort type 
| eval sAMAccountName=lower(owner) 
| stats count by title orphaned sAMAccountName sharing type owner updated app disabled 
| search orphaned=1
| join sAMAccountName
    [| search index=metrics_summary source="LDAP*" source IN("LDAP GROUP USER DIVISION Summary Index Search" "LDAP_GROUP_USER_DIVISION_Summary_Index_Search" lookup_ldap_group_user_division) sAMAccountName=e* OR sAMAccountName=v* NOT sAMAccountName=e-mail* userAccountControl=*ACCOUNTDISABLE* 
| eval sAMAccountName=lower(sAMAccountName) 
| stats latest(_time) AS latest values(displayName) values(mail) values(distinguishedName) values(department) values(division) latest(userAccountControl) values(manager) by sAMAccountName]

I needed to limit the results more in the subsearch. Thanks so much.