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
.
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.
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.
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!
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
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.
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.
Seems to be correct @cmerriman . Now I need the percentage value of both customerId's.
Thank you!
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
@cmerriman, I dont think double quotes for -1
is required. I expect the solution to be only casing issue with Field Name casing.
@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.
Hi @niketnilay,
I wrongly mentioned as customerID, correct value is customerId. I have corrected it and ran the query still not getting desired output.