Not able to map 2 different type of events

[nilbak1]

I have following below scenario

Different stages of orders placed happens in below sequence

1. order-process started ---- this event happens on different hosts say around 15 hosts -- say it happens for 800 different order IDs
2. orders placed ---- while this event happens only on 1 host ( which is other than those 15 hosts -- say it happens on 780 order IDs

Common thing among both events is order ID.

Now I want to find out the ordersplaced (2) by those different 15 hosts.
i.e 780 orders placed on those 15 hosts.

But I am not able to map this. Can someone help ?

[niketn · Accepted solution]

@nilbak1 you should better use stats for such correlations rather than join. With stats you can perform any type of join depending on your use case and correlation field/s.
Based on the minimal details in the question I can grasp that you need to correlate two events started and placed which for the same order may happen on two different hosts and correlation key is the order id. After correlation you need the host from which order started as well.

Please try out following run anywhere example which has four orders created i.e. order1,order2,order3,order4 and only two orders placed i.e. order1 and order3.
Commands from | makeresults till | eval are used to generate sample data as per the question.
The stats by order_id is used for correlation and first(host) as started_host gives the host which started the order as per the question. The filter search types IN ("started","placed") ensures the inner join i.e. for order_ids correlated, the events should come for both types i.e. started and placed.

| makeresults
| eval data="started,order1,host1;started,order2,host1;started,order3,host2;started,order4,host3;placed,order1,host2;placed,order3,host1"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval type=mvindex(data,0),order_id=mvindex(data,1),host=mvindex(data,2)
| stats values(type) as types first(host) as started_host last(host) as placed_host by order_id
| search types IN ("started","placed")

Please try out and confirm!

[nilbak1]

Thanks @niketnilay.
This worked perfectly 🙂

[nilbak1]

@niketnilay Can you help ?
I tried doing this with inner join but it didn't helped.