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

kumagaur — Wed, 19 Sep 2018 12:30:33 GMT

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.

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

datasearchninja — Thu, 20 Sep 2018 07:34:01 GMT

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_considerations

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

kumagaur — Thu, 20 Sep 2018 10:00:46 GMT

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.

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

kumagaur — Tue, 25 Sep 2018 11:30:58 GMT

Any update?

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

datasearchninja — Tue, 25 Sep 2018 22:27:13 GMT

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.

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

kumagaur — Wed, 26 Sep 2018 08:54:30 GMT

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?

topic After joining two counts with dbquery, why are the results getting truncated? in Splunk Search

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

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

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

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

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

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