topic Re: customerID percentage on a particular channelID based on specific event value in Splunk Search

customerID percentage on a particular channelID based on specific event value

iqbalintouch — Mon, 31 Jul 2017 04:56:10 GMT

I am running this query but not getting desired output.

index=myapp sourcetype=log_source host="*myhost*" "Event*" AND "sell event" channelId=xyz
| timechart count(customerId) as total_customer_searches, count(eval(customerID=-1)) as anonymous_customer_Searches
| eval anonymous_search_percent=((anonymous_customer_Searches/total_customer_searches)*100)

so, customerID could have -1 OR any positive integer value. there are multiple channelId ( abc, def, ghi... xyz ) and I need to get a timechart or stats count of the percentage for customerId=-1 on channelId=xyz.

Re: customerID percentage on a particular channelID based on specific event value

niketn — Mon, 31 Jul 2017 11:14:31 GMT

@iqbalintouch, field names are case sensitive. So, in case your field name is customerID, the count(customerId) should actually be count(customerID)

Also try out round() function for percentage to reduce to two digit precision or whatever you like.

| eval anonymous_search_percent=round(((anonymous_customer_Searches/total_customer_searches)*100),2)

Please try out and confirm.

Re: customerID percentage on a particular channelID based on specific event value

cmerriman — Tue, 29 Sep 2020 15:07:07 GMT

try something along these lines:

index=myapp sourcetype=log_source host="*myhost*" channelId=xyz ("Event*" AND "sell event" )
 | timechart count(customerID) as total_customer_searches, count(eval(customerID="-1")) as anonymous_customer_Searches
 | eval anonymous_search_percent=((anonymous_customer_Searches/total_customer_searches)*100)
|fillnull anonymous_search_percent value=0

using customerID instead of customerId and placing -1 inside quotations. I also added a fillnull incase some of the anonymous_search_percents were not filled out.

Re: customerID percentage on a particular channelID based on specific event value

niketn — Tue, 01 Aug 2017 02:10:14 GMT

@cmerriman, I dont think double quotes for -1 is required. I expect the solution to be only casing issue with Field Name casing.

Re: customerID percentage on a particular channelID based on specific event value

iqbalintouch — Tue, 01 Aug 2017 04:57:28 GMT

Hi @niketnilay,

I wrongly mentioned as customerID, correct value is customerId. I have corrected it and ran the query still not getting desired output.

Re: customerID percentage on a particular channelID based on specific event value

iqbalintouch — Tue, 01 Aug 2017 05:05:06 GMT

Hi @cmerriman,

Query is looking good but not getting desired output. Anonymous search percent is showing more than 80% which shouldn't be..most of the time it should be >=5%.

customerId can have any values like 123456, 6546788, -1, 765478 out of these customerId's only those searches with customerId=-1 are anonymous search. Hope this help !

Please let me know if you need more details.
Thank yoU!

Re: customerID percentage on a particular channelID based on specific event value

cmerriman — Tue, 29 Sep 2020 15:07:31 GMT

when i run this, i'm getting 7 total_customer_searches and 1 anonymous_customer_searches with 14%. That is accurate. if you break the syntax apart, pull in a small time range with a small set of data and run each line at a time, perhaps, make sure the data matches the output. If the counts aren't matching, let me know.

|makeresults|eval customerId="123456,6546788,765478,-1,1257862,4867354,5884368"|makemv customerId delim=","|mvexpand customerId|timechart  count(customerId) as total_customer_searches, count(eval(customerId="-1")) as anonymous_customer_Searches | eval anonymous_search_percent=((anonymous_customer_Searches/total_customer_searches)*100)|fillnull anonymous_search_percent value=0

Re: customerID percentage on a particular channelID based on specific event value

iqbalintouch — Wed, 02 Aug 2017 03:05:01 GMT

Hi @cmerriman,

Yes, if I take the above example it is working fine but when I add index, sourcetype, host and channelId the query is not working.
so in your query you need the string: index=myapp sourcetype=log_source host="myhost" "Event*" AND "sell event" channelId=xyz

so we just need to make it certain that anonymous customer percentage shouldn't be increasing on channelId=xyz.
NOTE: customerId can have -1 value or any positive numeric value...and these numbers can be in thousands and millions so we need to make a percent comparison of known and unknown customers.
In other words known(non -1) CUSTOMER_ID % should be at least 95%.

Hope this helps.

Re: customerID percentage on a particular channelID based on specific event value

cmerriman — Wed, 02 Aug 2017 13:31:54 GMT

try running this and see if you get the expected outcome where customerId of -1 has a value of 1 in IsOfConcern and all other customerIds have a value of 0:

index=myapp sourcetype=log_source host="*myhost*" channelId=xyz (Event* AND "sell event" ) | eval IsOfConcern = if(customerId="-1", 1, 0) | table customerId, IsOfConcern

if you're seeing the correct values and the number of rows you might expect in total, then everything should be accurate. Otherwise we might have a problem with the base search/source data.

Re: customerID percentage on a particular channelID based on specific event value

iqbalintouch — Thu, 03 Aug 2017 09:02:32 GMT

Seems to be correct @cmerriman . Now I need the percentage value of both customerId's.

Thank you!

Re: customerID percentage on a particular channelID based on specific event value

cmerriman — Thu, 03 Aug 2017 11:48:26 GMT

you can use the IsOfConcern and sum those up, if you'd like.
something like (if it doesn't appear correct, start doing it line by line until you see where it looks wrong):

 index=myapp sourcetype=log_source host="*myhost*" channelId=xyz (Event* AND "sell event" ) | eval IsOfConcern = if(customerId="-1", 1, 0)| table customerId, IsOfConcern|stats sum(IsOfConcern) as anonymous_customer_Searches count as total_customer_searches| eval anonymous_search_percent=((anonymous_customer_Searches/total_customer_searches)*100)

if you need it by _time, you can sub stats for timechart, or add |bucket _time span=1h|stats ...... by _time