field from join [dbxquery ....] is not showing in final table results

[odigokid]

I am trying to perform a query from DB1 and then join a 2 fields from DB2 - the SQL queries are working fine, but when I add the join in splunk the fields from DB2 are not showing in the final table results. Here is my SPL

| 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

Can't seem to figure out why milestonecount is not getting added to events as a column - I am expecting "Incident_Number, order_id, order_type, milestonecode, and lastupdateddate as fields in each event (first 3 come from the first query and last 2 from the second query)

[cmerriman · Accepted solution]

i think part of the problem is you are joining on order_id but your queries have ORDERID. when you run the queries individually in Splunk, make sure the types are the same - they both come out as ORDERID or OrderID, etc. If they don't, use |rename ORDERID as OrderID or something similar to rename them as the same field, and then change the join OrderID type=left, you'll need to rename ORDERTYPE as well, if you want it as order_type

[odigokid]

Thanks this worked.