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, an upvote 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, an upvote would be appreciated.

View solution in original post

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, an upvote would be appreciated.
0 Karma

kirthi_d
Engager

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

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!