All Apps and Add-ons

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 Incident_Number, Detailed_Decription FROM HPD_Help_Desk WHERE Assigned_Group = 'Order Processing' AND Reported_Date between $info_min_time$ AND $info_max_time$\" connection=\"ITSM\"| rex field=Detailed_Decription \"ORDERID-->(?\S+).*ORDERTYPE--->(?\S+)\" | join order_id type=left [| dbxquery shortnames=true connection=\"AUXDB\" query=\"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\" | fields milestonecount, lastupdateddate]"
| table Incident_Number, order_id, order_type, 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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!