Splunk Search

Relevance of Ratings Based on Different Weights by ID

RKB1923
Engager

Hi, beginner here having problems trying to write a query.

In my data, I have an event that records when an app is rated (the event records id and rating from one to five stars). I want to weigh the ratings like so:

5-star or 1-star = important (2 point)
4-star or 2-star = semi_important (1.5 points)
3-star = not_important (1 points)

I'm weighing the star-ratings unequally as I believe those who are extremely satisfied/dissatisfied will tend to be more vocal and those having average experiences will not be. I want the query to return the apps that need the most attention, good or bad.

Based on those points, I'd like to create a relevance score:
relevance = important + semi_important + not_important

I'd like to return a table with the following columns:
id, rating, average_rating, relevance

I can get the first three columns with the following, but don't know how to get my relevance score:

evt_name="RateApplet" | stats count("evt_name") as times_rated avg("rating") as average_rating by applet_id

Thank you for any guidance anyone can give me (or any suggestions to improve).

Tags (1)

yannK
Splunk Employee
Splunk Employee

for each app, do a count by rating.

evt_name="RateApplet" | stats count("evt_name") by applet_id rating

it will return something like
applet_id | rating | count

Then you can use an nice eval function to calculate your weight (or define a macro)

| eval rating_weight=case(rating=3 OR rating=4,1.5, rating=5 OR rating=1,2,1=1,0)
| eval weight=count*rating_weight

And finally do another stat to sum the weight per app.

| stats sum(weight) by applet_id

the total search looks like,

evt_name="RateApplet" | stats count("evt_name") by applet_id rating
| eval rating_weight=case(rating=3 OR rating=4,1.5, rating=5 OR rating=1,2,1=1,0)
| eval weight=count*rating_weight
| stats sum(weight) by applet_id

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