topic Re: Return items not present in a subsearch in Getting Data In

Why Return items not present in a subsearch?

psimoes — Wed, 12 Apr 2023 16:28:20 GMT

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?

Re: Return items not present in a subsearch

yeahnah — Wed, 12 Apr 2023 01:36:36 GMT

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

Re: Return items not present in a subsearch

gcusello — Wed, 12 Apr 2023 06:26:30 GMT

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

Re: Return items not present in a subsearch

manjunathmeti — Wed, 12 Apr 2023 10:32:56 GMT

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

Re: Why Return items not present in a subsearch?

woodcock — Thu, 13 Apr 2023 15:59:55 GMT

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