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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...