How can I search for multiple values present in different fields? For example, I have fields titled FinalPurchases and Cart and I would like to find out how many people had products in their carts that went on to purchase those products. I hope this example is clear enough.
This is based on this other Q&A:
Try this (presuming you have a
joiner field called
sessionID but this might be
username or something else):
| makeresults | eval sessionID = "1 2 3 4 5" | makemv sessionID | mvexpand sessionID | multireport [ eval cart = case( sessionID==1, "a b c", sessionID==2, "d e f", sessionID==3, "g h i", sessionID==4, "h i j", sessionID==5, "a h z") | makemv cart ] [ eval FinalPurchases = case( sessionID==1, "a b c", sessionID==2, "e", sessionID==4, "a i z", sessionID==5, "x y z") | makemv FinalPurchases ] | reverse | rename COMMENT AS "Everything above generates sample events; everything below is your solution" | selfjoin sessionID | streamstats count AS _serial | multireport [| mvexpand FinalPurchases | where cart!=FinalPurchases | rename FinalPurchases AS removed] [| mvexpand cart | where cart!=FinalPurchases | rename cart AS added] | fields - cart FinalPurchases | stats first(_time) AS _time first(sessionID) AS sessionID values(*) AS * BY _serial
Here's some pseudocode ...
(your search that gets the first kind of event) OR (your search that gets the second kind of event) | rename COMMENT as "cut down the data to just what you need" | fields (list all the fields that you need from either kind of event) | rename COMMENT as "set up a match key if the field has different names on the two records" | eval matchkey = if(is is the first kind of event, the key from first event, the key from second event) | rename COMMENT as "Set up a flag for which kind of record it is, unless there is already an appropriate single field" | eval flag=if(it is the first kind of event, "firsttype","secondtype") | rename COMMENT as "Use stats to roll together all the matched records" | stats ...whatever kind of other fields you need .... values(flag) as flag by matchkey | rename COMMENT as "Now you have a single record for each value of matchkey that you can analyze"
You can also use one or more than one existing field as the match key or the flag, like the example below...
Now, this can be really simple if the fields are already there. Let's suppose that there is one sourcetype for the first kind of record and a different sourcetype for the second, and the field being matched is the same on both events, let's say sessionID... but there may be multiple records with different Product fields, and you need to know which were and were not purchased.
(index="foo" sourcetype="Purchase") OR (index="foo" sourcetype="Cart") | fields sourcetype sessionID Product | stats values(sourcetype) as sourcetype by sessionID Product | rename COMMENT as "the above has created one line for each sessionID/Product combination"
Now we filter to ONLY the Products that were purchased...
| where mvcount(sourcetype) > 1
Or, if we want to keep ALL records whenever ANY of them were purchased
| eval bought=case(mvcount(sourcetype)>1,1) | eventstats max(bought) as boughtsome by sessionID | where isnotnull(boughtsome)
Or, if we want to know only the ones that were dropped
| where mvcount(sourcetype) = 1 AND sourcetype="Cart"
..and so on...