Splunk Search

How can I modify this query without the join command?

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.

Splunk Employee
Splunk Employee


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.

0 Karma


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

0 Karma


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
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!