Hi All,
I have a CSV file with the following information
date,retailer,lower,upper,price_rate
20120621,retailer1,0,10,10
20120621,retailer1,10,20,8
20120621,retailer1,20,100,5
20120621,retailer2,0,10,10
20120621,retailer2,10,20,8
20120621,retailer2,20,100,5
Now there are Splunk events which contains hourly usage
_time, retailer, usage
So I want to get a sum of all the usage over one month with something like
sourcetype="myData" | stats sum(usage) by retailer
But I then want to calculate a total cost.
So for example the if the total usage over the month was 50.
How can I do this in a Splunk search?
Harder still the original CSV was actually intended to be a time based lookup so it would only use the associated date section based on the _time from the events.
I am thinking maybe I should restructure this CSV and join it 3 times in the props.conf somehow???
Any assistance would be greatly appreciated.
Hi
im skipping the time based part for now. If you do the lookup after your stats command:
sourcetype="myData" | stats sum(usage) as sum by retailer | lookup retail retailer
the lookup will add the tiered pricing information as multivalued fields.
You can append this eval to get your result ( I don't think, that Splunk has any looping operators that can be aplied to an event/the mv fields of an event):
|eval price=case (
sum>mvindex(lower,0) AND sum<=tonumber(mvindex(upper,0)),tonumber(mvindex(price_rate,0))*sum,
sum>=tonumber(mvindex(lower,1)) AND sum<=tonumber(mvindex(upper,1)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+(sum-tonumber(mvindex(upper,0)))*tonumber(mvindex(price_rate,1)),
sum>=tonumber(mvindex(lower,2)) AND sum<=tonumber(mvindex(upper,2)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+tonumber(mvindex(price_rate,1))*(tonumber(mvindex(upper,1))-tonumber(mvindex(upper,0)))+(sum-tonumber(mvindex(upper,1)))*tonumber(mvindex(price_rate,2))
)
This obviously only works if you you have only 3 ranges and gets more complex with every additional range you add.
Hi
im skipping the time based part for now. If you do the lookup after your stats command:
sourcetype="myData" | stats sum(usage) as sum by retailer | lookup retail retailer
the lookup will add the tiered pricing information as multivalued fields.
You can append this eval to get your result ( I don't think, that Splunk has any looping operators that can be aplied to an event/the mv fields of an event):
|eval price=case (
sum>mvindex(lower,0) AND sum<=tonumber(mvindex(upper,0)),tonumber(mvindex(price_rate,0))*sum,
sum>=tonumber(mvindex(lower,1)) AND sum<=tonumber(mvindex(upper,1)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+(sum-tonumber(mvindex(upper,0)))*tonumber(mvindex(price_rate,1)),
sum>=tonumber(mvindex(lower,2)) AND sum<=tonumber(mvindex(upper,2)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+tonumber(mvindex(price_rate,1))*(tonumber(mvindex(upper,1))-tonumber(mvindex(upper,0)))+(sum-tonumber(mvindex(upper,1)))*tonumber(mvindex(price_rate,2))
)
This obviously only works if you you have only 3 ranges and gets more complex with every additional range you add.
I'm thinking the best solution is to just change the CSV format to.
date,retailer,upper_tier1,price_tier1,,upper_tier2,price_tier2,upper_tier3,price_tier3
Then use a similar method as you suggested. That will allow me to use temporal lookups without worrying about changes in the number of tiers. If they add additional tiers I will just have to change my searches.
Thanks again for the assist.
Nice that works perfectly. A good example of how multikv works too.
My only issue now is the using this as a temporal style lookup.
Turns out it is possible by adding a max_matches=3 to the transforms.conf . Again however as you mentioned if some retailers have less/more tiers than others it breaks the whole system.