topic Re: How can I modify this query without the join command? in Splunk Search

How can I modify this query without the join command?

uhkc777 — Mon, 24 Oct 2016 19:20:09 GMT

index=Pharma_ParMed_STG sourcetype=ParMed-SalesOrder source="DBX:ParMed-Stage" 
|table OrderEntryDate OrderId OrderDetailID CAHInventoryNum OrderedQuantity 
| join  type=outer OrderId OrderDetailID [
       |search index=Pharma_ECC_STG sourcetype=SAP-SalesOrder source="DBX:SE8"
       |eval itemid=ITEMDETAILID,salesordernum=SALESORDERNUM
       |rename ITEMDETAILID as OrderDetailID,SALESORDERNUM as OrderId
       |table OrderId OrderDetailID salesordernum]
 |search NOT salesordernum=*
 |fields - salesordernum|

It simply shows the missing orders between this 2 indexes.

Re: How can I modify this query without the join command?

sundareshr — Mon, 24 Oct 2016 19:34:42 GMT

Try this

(index=Pharma_ParMed_STG sourcetype=ParMed-SalesOrder source="DBX:ParMed-Stage" ) OR (index=Pharma_ECC_STG sourcetype=SAP-SalesOrder source="DBX:SE8") | eval OrderDetailID=coalesce(OrderDetailID, ITEMDETAILID) | eval  OrderId = coalesce(OrderId, SALESORDERNUM) | stats dc(sourcetype) as missing latest(OrderEntryDate) as OrderEntryDate latest(OrderId) as OrderId latest(CAHInventoryNum) as CAHInventoryNum latest(OrderedQuantity) as OrderedQuantity by OrderId OrderDetailID | where missing<2

Re: How can I modify this query without the join command?

MuS — Mon, 24 Oct 2016 19:43:18 GMT

Hi uhkc777,

you can use something like this - sorry not tested and it is early morning for me so don't blame me if it does not work straight away 😉

index=Pharma_ParMed_STG sourcetype=ParMed-SalesOrder source="DBX:ParMed-Stage"  OR index=Pharma_ECC_STG sourcetype=SAP-SalesOrder source="DBX:SE8"
| eval OrderId = coalesce(OrderId, SALESORDERNUM)
| eval OrderDetailID = coalesce(OrderDetailID, ITEMDETAILID)
| stats count(index) AS c_idx by OrderId OrderDetailID
| where c_idx = 1

Hope this gets you started ...

cheers, MuS

Re: How can I modify this query without the join command?

aaraneta_splunk — Mon, 24 Oct 2016 20:06:39 GMT

@uhkc777,

Our community moderators noticed that you have posted duplicate questions regarding the join command.
- This post: https://answers.splunk.com/answers/468914/how-can-i-modify-this-query-without-the-join-comma.html
- Duplicate: https://answers.splunk.com/answers/468917/how-to-modify-this-query-without-join-command.html

Please refrain from posting duplicate questions as this creates unnecessary clutter on the forum.

Though the duplicate question (second link) has already been closed, I will be deleting the question now.