All Apps and Add-ons
Highlighted

second dbxquery fields not showing up in table results

Engager

Trying to figure out why "milestonecode" is not showing up in the final table results

| makeresults

| addinfo

| map search="dbxquery query=\"SELECT IncidentNumber, DetailedDecription FROM HPDHelpDesk WHERE AssignedGroup = 'Order Processing' AND ReportedDate between $infomintime$ AND $infomaxtime$\" connection=\"ITSM\"| rex field=DetailedDecription \"ORDERID-->(?\S+).*ORDERTYPE--->(?\S+)\" | join orderid type=left [| dbxquery shortnames=true connection=\"AUXDB\" query=\"SELECT oh.lastupdateddate AS lastupdateddate, NVL(om.rowcount, 0) as milestonecount FROM PLPRODSER1.ORDERHEADER oh LEFT OUTER JOIN (SELECT orderid, COUNT(orderid) AS rowcount FROM PLPRODSER1.ORDERMILESTONE WHERE UPPER(MILESTONECODE) IN ('PAYMENTPOSTED','PAYMENTXFERRED','REFUNDPOSTED','BACKOUTAPPLIED','PSTVOIDCOVADJ','EXCHANGECHGADJ','ACCOUNTREFILLED','BACKOUTCHGADJ','BALANCEDXFERRED','CHARGEADJCARE','BACKOUTREVERSAL','BACKOUTNONCOVADJ','D2AEPINCOVADJ','PAYMENTERROR') AND STATUS = 'SUCCESS' GROUP BY orderid) om ON oh.orderid = om.orderid\" | fields milestonecount, lastupdateddate]"
| table Incident
Number, orderid, ordertype, milestonecount

The second query has been verified using dbconnect and its returning back the proper fields with counts

SELECT oh.lastupdateddate AS lastupdateddate, NVL(om.rowcount, 0) as milestonecount FROM PLPRODSER1.ORDER_HEADER oh LEFT OUTER JOIN (SELECT orderid, COUNT(orderid) AS rowcount FROM PLPRODSER1.ORDERMILESTONE WHERE UPPER(MILESTONECODE) IN ('PAYMENTPOSTED','PAYMENTXFERRED','REFUNDPOSTED','BACKOUTAPPLIED','PSTVOIDCOVADJ','EXCHANGECHGADJ','ACCOUNTREFILLED','BACKOUTCHGADJ','BALANCEDXFERRED','CHARGEADJCARE','BACKOUTREVERSAL','BACKOUTNONCOVADJ','D2AEPINCOVADJ','PAYMENTERROR') AND STATUS = 'SUCCESS' GROUP BY orderid) om ON oh.orderid = om.orderid

and the first query is also working fine, whenI remove the join logic

the final result is showing the first 3 columns, but milestonecount is blank

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.