Why Return items not present in a subsearch?

[psimoes]

Given the simple scenario:

I have users in a platform that have actions, I want to return all the users that haven't performed a specific action. For example, I want to return all users that have been doing the action "View", but haven't performed any action "Purchase". The index would be the same.

Pseudo query:

index=A action=view [ search index=A action=purchase | stats count by user ] | where count by user = 0 

Any idea how could I write such query?

[woodcock]

Like this:
| makeresults
| eval _raw="
user action
A view
A purchase
B view
C purchase"
| multikv forceheader=1
| stats count(eval(action="view")) AS views count(eval(action="purchase")) AS purchases BY user
| where views>0 AND purchases==0

So like this:
index="A" AND (action="view" OR action="purchase")
| stats count(eval(action="view")) AS views count(eval(action="purchase")) AS purchases BY user
| where views>0 AND purchases==0

[manjunathmeti]

hi @psimoes ,

As @yeahnah and @gcusello mentioned, you don't need a sub-search for this unless your action=purchage events are in a different index/sourcetype.

Try this.

| makeresults 
| eval _raw="user,action
A,view
B,view
C,view
C,purchase
D,purchase" 
| multikv forceheader=1 
| fields - _raw, linecount 
| eventstats count(eval(action="view")) as view_count, count(eval(action="purchase")) as purchase_count by user 
| where purchase_count=0

[gcusello]

Hi @psimoes,

as @yeahnah said, this is an incorrect way to use subsearches and anyway, you don't need a subsearch for your purpose.

Please try something like this:

index=A (action=view OR action=purchase)
| stats dc(action) AS action_count values(action) AS action BY user 
| where action_count=1 AND action=view

 Ciao.

Giuseppe

[yeahnah]

Hi @psimoes 

No need for a subsearch to do this - avoid subsearches where possible as less efficient.

Try this...

index=A action=view OR action=purchase
| stats count values(action) AS actions BY user
| eval purchase_made=if(isnotnull(mvfilter(match(actions, "purchase"))), "yes", "no")
| where purchase_made="no"

The actions field is a multivalue field and the if statement tests whether this field contains the purchase value or not, before the where filter is applied.

Hope it helps