topic Re: Question regarding join in Splunk Search

Question regarding join

therockhead — Mon, 28 Sep 2020 19:16:55 GMT

Beginner here, I'm trying to do the following in one query
1) Get all unique users and the count of users
2) Using all the unique users from query 1, search for who has done action "shop"
3) Show % of people who did the action "shop"

This is what i have tried so far ..

sourceTag=test index=test | stats dc(user_id) AS "Total Users" | JOIN user_id [search sourceTag=test index=test ACTION="shop" | stats dc(user_id) AS "Shoppers Users" ] | eval PercentageOfUsers=round(tonumber('Shopper Users') * 100 / tonumber('Total Users'))

Any ideas whats wrong?
Thanks,
Bill

Re: Question regarding join

masonmorales — Mon, 28 Sep 2020 19:20:07 GMT

Yes, your first command is only going to return a number of distinct user_ids. This means that you no longer have user_id to join on, since the output of your stats command is just a number. Same for the output of your JOIN command. The output is a number, so there are no user_id field values to join on.

Does this work for you?

sourceTag=test index=test 
| table user_id 
| dedup user_id 
| JOIN user_id [search sourceTag=test index=test ACTION="shop" | table user_id] 
| fillnull 
| stats count(eval(ACTION="shop")) as ShopperCount, count(eval(ACTION!="shop")) as NonShoppers
| eval TotalUsers=ShopperCount+NonShoppers
| eval PercentageOfUsers=round(ShopperCount/TotalUsers*100,2)

You can also add renames at the end to give the fields your desired labels. i.e.
| rename PercentageOfUsers as "% of Users that Shopped"

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 14:34:45 GMT

Thanks for response but it did not work for me. The join is only returning the user_id and no action data so that stats always return 0.

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 14:42:21 GMT

Any chance you could post a few lines of sample data from each source? (Feel free to anonymize field data)

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 14:59:19 GMT

Sure .. for example here are two separate events

message: {
context: {
user_id: 443d5c6c-32ad-460e-a387-b077e9c47f3f
....
}
time: 2015-03-24T14:51:58.721Z
action: shop
}

message: {
context: {
user_id: 443d5c6c-32ad-460e-a387-b077e9c47f3f
.....
}
time: 2015-03-24T14:51:58.721Z
action: login
}

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 15:23:33 GMT

Okay, sorry about that. Try this instead:

 sourceTag=test index=test 
 | table user_id 
 | dedup user_id
 | JOIN user_id type=outer [search sourceTag=test index=test ACTION="shop" | table user_id ACTION] 
 | fillnull value=NULL ACTION 
 | stats count(eval(ACTION="shop")) as ShopperCount, count(eval(ACTION!="shop")) as NonShopperCount
 | eval TotalUsers=ShopperCount+NonShopperCount
 | eval PercentageOfUsers=round(ShopperCount/TotalUsers*100,2)

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 15:36:28 GMT

If this worked for you, please click "Accept Answer"

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 15:44:59 GMT

Just trying it out now .. its looking better, the join is returning data that looks correct but the stats is not working.

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 15:53:14 GMT

Could you provide more detail on the behavior you are seeing with stats please?

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 15:55:57 GMT

So if it dont include stats the data format looks correct. If it add the following for example - stats count(eval(ACTION="shop")) as ShopperCount the result will be 0

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 15:57:06 GMT

Also count(eval(ACTION!="shop")) as NonShopperCount returns 0 as well.

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 15:59:41 GMT

Also tried with extra equals - stats count(eval(ACTION=="shop"))

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 16:01:54 GMT

I extracted ACTION in my test environment with your data and it worked fine. I am guessing you have a different field name for ACTION than what was posted, or that it's not all uppercase. Field names are case sensitive. Could you please verify that the case/spelling of your extracted field for "ACTION" matches that of the example? If it does not, replace all occurrences of "ACTION" in my example with exactly how the "action" field name appears in your environment.

Re: Question regarding join

masonmorales — Tue, 24 Mar 2015 16:06:48 GMT

Alternatively, you could do:

 sourceTag=test index=test 
  | table user_id 
  | dedup user_id
  | JOIN user_id type=outer [search sourceTag=test index=test | rex "(?i)action: (?P<ACTION>[^ ]+)" | search ACTION="shop" | table user_id ACTION] 
  | fillnull value=NULL ACTION 
  | stats count(eval(ACTION="shop")) as ShopperCount, count(eval(ACTION!="shop")) as NonShopperCount
  | eval TotalUsers=ShopperCount+NonShopperCount
  | eval PercentageOfUsers=round(ShopperCount/TotalUsers*100,2)

Re: Question regarding join

therockhead — Tue, 24 Mar 2015 16:26:56 GMT

Ok the issue was my Action was nested and the eval didnt like that .... so
stats count(eval(body.ACTION="shop")) would not work
I renamed body.ACTION to ACTION
and changed my query to
count(eval(ACTION="shop"))
and it now works great. Thanks for all your help!!