All Apps and Add-ons

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

Engager

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)

0 Karma
1 Solution

Super Champion

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

View solution in original post

Super Champion

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

View solution in original post

Engager

Thanks this worked.

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!