Splunk Search
Highlighted

customerID percentage on a particular channelID based on specific event value

Path Finder

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.

Tags (2)
0 Karma
Highlighted

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

Legend

@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.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

Path Finder

Hi @niketnilay,

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

0 Karma
Highlighted

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

Super Champion

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 anonymoussearchpercents were not filled out.

View solution in original post

0 Karma
Highlighted

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

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

Path Finder

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!

0 Karma
Highlighted

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

Super Champion

when i run this, i'm getting 7 totalcustomersearches and 1 anonymouscustomersearches 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
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

Super Champion

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.

Highlighted

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

Path Finder

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

Thank you!

0 Karma