How to calculate a value from 2 different sources?

[kirthi_d]

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.

[richgalloway · Accepted solution]

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

[kirthi_d]

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

[richgalloway]

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.

[kirthi_d]

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