topic Re: Alternative for join. in Splunk Search

Alternative for join.

sandeepmakkena — Wed, 30 Sep 2020 02:19:24 GMT

index=core a=BuilderService AND "decision.received" "Overrides" NOT "ItemOverrides=()" NOT commitCode=null
| rename Orderid as data.Orderid
| join data. Orderid
[ search index=transaction "mileStoneId"=SESSION_CREATE
| rename commitCode as CommitCodeAtSessionCreate]
| join data. Orderid
[ search index=transaction "mileStoneId"="ORDER_RELEASE"
| rename commitCode as CommitCodeAtWebOrderRelease]
| table data.Orderid,CommitCodeAtSessionCreate,commitCode,data.webOrderNumber,CommitCodeAtWebOrderRelease
| rename commitCode as OverridenCommitCode

I am trying to print commitCode at different stages of order placement.This works but, I just don't want to use join.
Thanks for your time.

Re: Alternative for join.

DavidHourani — Mon, 23 Sep 2019 06:53:12 GMT

Hi @sandeepmakkena,

Try something like this :

(index=core a=BuilderService AND "decision.received" "Overrides" NOT "ItemOverrides=()" NOT commitCode=null)
OR
(index=transaction "mileStoneId"=SESSION_CREATE)
OR
(index=transaction "mileStoneId"="ORDER_RELEASE")
|rename data.Orderid as Orderid, data.webOrderNumber as webOrderNumber
|eval CommitCodeAtSessionCreate=if(mileStoneId="SESSION_CREATE",mileStoneId,null())
|eval CommitCodeAtWebOrderRelease=if(mileStoneId="ORDER_RELEASE",mileStoneId,null())
|stats values(CommitCodeAtSessionCreate) as CommitCodeAtSessionCreate values(commitCode) as commitCode values(CommitCodeAtWebOrderRelease) as CommitCodeAtWebOrderRelease values(webOrderNumber) as webOrderNumber by Orderid

Let me know if that helps.

Cheers,
David

Re: Alternative for join.

sandeepmakkena — Wed, 30 Sep 2020 02:19:43 GMT

Thanks for your time Devid, but I want to print commitCodes for orders only that have decision received orders not for all the weborders.

Just to make clear I want to print commitcodes for the orders that, has milestoneID Session_create or Order_release and have a decision received and Overrider before.

Re: Alternative for join.

DavidHourani — Tue, 24 Sep 2019 03:52:04 GMT

In that case all you need to do is include an extra check to insure that the orderid is in both indices :

 (index=core a=BuilderService AND "decision.received" "Overrides" NOT "ItemOverrides=()" NOT commitCode=null)
 OR
 (index=transaction "mileStoneId"=SESSION_CREATE)
 OR
 (index=transaction "mileStoneId"="ORDER_RELEASE")
 |rename data.Orderid as Orderid, data.webOrderNumber as webOrderNumber
 |eval CommitCodeAtSessionCreate=if(mileStoneId="SESSION_CREATE",mileStoneId,null())
 |eval CommitCodeAtWebOrderRelease=if(mileStoneId="ORDER_RELEASE",mileStoneId,null())
 |stats dc(index) as condition values(CommitCodeAtSessionCreate) as CommitCodeAtSessionCreate values(commitCode) as commitCode values(CommitCodeAtWebOrderRelease) as CommitCodeAtWebOrderRelease values(webOrderNumber) as webOrderNumber by Orderid
|where condition >1