After joining two counts with dbquery, why are the results getting truncated?

[kumagaur]

I have one query where I want to get the results in terms of installed,uninstalled and exception machines.

We are getting not installed machine details from a Lookup file, and exception machine details are from an LDAP search. We are getting installed machine details from dbquery.

When I am joining installed and exceptions machine count with dbquery — which is having more than 70K results — the final results are getting truncated and I am getting partial results.

| inputlookup newuptodatead.csv  
| where strptime(pwdLastSet,"%Y/%m/%d %H:%M:%S %Z")>=relative_time(now(),"-45d@d") 
| rex field=distinguishedName "CN=.*?,DC=(?.*?),DC="|search DC=na OR DC=ap OR DC=eu OR DC=sa OR DC=mea
| rex field=distinguishedName "CN=.*?,OU=(?.*?),DC="
| search ADOU="*EngineeringLabs*" AND ADOU!="*Harvester*"  AND ADOU!="*Image*" AND operatingSystem!="*Server*" AND operatingSystem="Windows*" AND cn != "*Kratos*" AND cn != "*harv*" 
| eval InWBSN="Not Installed"| eval cn=upper(cn)   
| join cn type=left [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=WBSN_RM))" attrs="member" | rename _raw as _raw2 | fields member | append [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=wbsn_rm_bsod))" attrs="member" | rename _raw as _raw2 | fields member ] | append [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=wbsn_rm_tomcat))" attrs="member" | rename _raw as _raw2 | fields member ] 
| stats values(member) as dn 
| rex field=dn "CN=(?.*?),OU=" 
|table cn 
|mvexpand cn 
|eval cn=upper(cn)
| eval InWBSN= " Exception"
|  sort 0 cn]
| join cn type=left [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=WBSN_RM))" attrs="member" | rename _raw as _raw2 | fields member | append [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=wbsn_rm_bsod))" attrs="member" | rename _raw as _raw2 | fields member ] | append [
| ldapsearch domain=x search="(&(objectCategory=group)(cn=wbsn_rm_tomcat))" attrs="member" | rename _raw as _raw2 | fields member ] 
| stats values(member) as dn 
| rex field=dn "CN=(?.*?),OU=" 
|table cn
|mvexpand cn 
| eval cn=upper(cn)
|eval InWBSN= " Exception"
|  sort 0 -cn]
| join cn type=left
 [ | dbquery "AA81" " SELECT [KEY] AS cn, UPDATE_DATE AS _time    FROM PA_DYNAMIC_STATUS   WHERE UPDATE_DATE >= DATEADD(DAY,-60,GETDATE())   "
 | append maxout=0  maxtime=0
 [ | dbquery "AA"    "  SELECT [KEY] AS cn,  UPDATE_DATE AS _time  FROM PA_DYNAMIC_STATUS   WHERE UPDATE_DATE >= DATEADD(DAY,-60,GETDATE())   "   ] 
 | rex field=cn "(?[^\\.]*)" 
 | eval cn=trim(upper(cn))
 | eval InWBSN="Installed" 
 | sort 0 cn ]
 | join cn type=left
 [ | dbquery "AA81"  "  SELECT [KEY] AS cn,  UPDATE_DATE AS _time  FROM PA_DYNAMIC_STATUS   WHERE UPDATE_DATE >= DATEADD(DAY,-60,GETDATE())   "  
 | append 
 [ | dbquery "AA"  "     SELECT [KEY] AS cn,  UPDATE_DATE AS _time  FROM PA_DYNAMIC_STATUS   WHERE UPDATE_DATE >= DATEADD(DAY,-60,GETDATE())   "   ]  
 | rex field=cn "(?[^\\.]*)"
 | eval cn=trim(upper(cn))
 | eval InWBSN="Installed"
 | sort 0 -cn]| stats Count by InWBSN

Result should be like:
InWBSN count
Installed ****
Not Installed *****
Exception *****

I am getting partial result when I am incorporating my search with Dbquery search.

[datasearchninja]

Subsearches like you are calling with 'join' and 'append' are limited to 10000 rows by default. See http://docs.splunk.com/Documentation/Splunk/latest/Search/Aboutsubsearches#Subsearch_performance_con...

[kumagaur]

I dont have any issues in any of the searches mentioned above..but when I am joining my 3rd |dbquery which has more 70K results on basis of 'cn' then the final result getting truncated.

[kumagaur]

Any update?

[datasearchninja]

70000 is greater than the maximum of 10000 for a subsearch. You either need to transpose your results in the subsearch so that rows become columns, or move the large output to be the first query so it is no longer a subsearch.

[kumagaur]

Actually my last subsearch is mandate filter which I can't make it as a first query 😞 and making the rows to column wont allow me to join it on a common field basis (eg; cn).
Is it possible to get the output without subsearch?