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.
... View more