Splunk Search

customerID percentage on a particular channelID based on specific event value

iqbalintouch
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
1 Solution

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

View solution in original post

0 Karma

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

0 Karma

iqbalintouch
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

cmerriman
Super Champion

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

iqbalintouch
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

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

iqbalintouch
Path Finder

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

Thank you!

0 Karma

cmerriman
Super Champion

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

iqbalintouch
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...