Splunk Search

How can I modify this query without the join command?

uhkc777
Explorer
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.

aaraneta_splunk
Splunk Employee
Splunk Employee

@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.

0 Karma

MuS
SplunkTrust
SplunkTrust

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

sundareshr
Legend

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
Get Updates on the Splunk Community!

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...