How to join 2 events and retrieve the status?

[Shashank_87]

Hi,

I have a scenario where I need to check if a customer has placed an order when he has been offered an offer.
So suppose there are total 100 customers who has been offered a particular offer and 40 of them placed an order but rest of them have not.
I need a query to show all those customers session ID in a tabular format and a column at the end which either says "Completed" OR "Not Completed". Here are the 2 queries -

1. This fetches all customer who have this offer in their basket.
index=test sourcetype="test_log" OR "Cart: at checkout" "OFFER1"
| dedup sessionID

2. This tells if a customer has placed an order
index=test sourcetype="test_log" "Order placed for Order num*".

I need something like this -

sessionID               offer                STATUS
23455454AXS OFFER1            Completed
45565MCHDA       OFFER1            NOT Completed

Any help would be much appreciated.

[HiroshiSatoh · Accepted solution]

I do not know the field to determine the status, but I think that it can be edited in STATS.

index=test sourcetype="test_log" |eval STATUS=if(XXXXX,"Completed","NOT Completed")
|table sessionID ,offer ,STATUS
|stats latest(*) as * by sessionID