Splunk Search

How to calculate a value from 2 different sources?

kirthi_d
Engager

I am pretty new to splunk. It would be great if someone can help me with a search command. I have productId as one of fields in my index data. I added lookup from products.csv which has productId, product_name & price. Now I need a bar chart with top 5 most selling products. How to get the total price from count and price? My current search is as below:

index=* action="purchase" |lookup products productId as productId OUTPUT product_name price | top limit =5 product_name

The above query gives me count of purchase transactions by productId. How to get the total sale amount for each product. something like:
Product Name & Sale price (count by productId * price).
Also how to make each bar a different color. The below is not helping:
option name="charting.seriesColors">[0x12BF05,0xEAD619,0xBF2105,0xD71F93,0xC294B1]

Thanks in advance.

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

To get the total sale, first count the number of sales for each product then multiply that number by the price.

index=* action="purchase" |lookup products productId as productId OUTPUT product_name price 
| top limit =5 product_name
| stats count values(price) as price by product_name
| eval totalSale = count * price
| table product_name count price totalSale
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

To get the total sale, first count the number of sales for each product then multiply that number by the price.

index=* action="purchase" |lookup products productId as productId OUTPUT product_name price 
| top limit =5 product_name
| stats count values(price) as price by product_name
| eval totalSale = count * price
| table product_name count price totalSale
---
If this reply helps you, Karma would be appreciated.
0 Karma

kirthi_d
Engager

Thanks. For some reason when I use top, it doesn't display price or totalSale. I removed top and replaced with sort and head 5. It is not the best way to do, but dont know why top is creating problem

0 Karma

richgalloway
SplunkTrust
SplunkTrust

top is a transforming command, similar to stats, meaning it only returns the fields explicitly named in the arguments. Usually, when top limit=5 foo is used, they really want sort 5 foo.

---
If this reply helps you, Karma would be appreciated.
0 Karma

kirthi_d
Engager

got it. I included the fields and looks perfect now. Thanks much..

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