Am trying write search across two sourcetype so that the customer event value has a multiplier for each day in the from/to date range.
For example,
sourcetype=CustomerEvent
Cust from-date to-date value
---- --------- ------- -----
A 2013-09-01 2013-09-03 123.45
B 2013-09-03 2013-09-04 543.21
sourcetype=Mulipliter
multiplier-date multiplier
--------------- ----------
2013-08-30 0.123
2013-09-01 0.321
2013-09-02 0.234
2013-09-03 0.432
2013-09-04 0.345
2013-09-05 0.534
Result needed (value * multiplier): -
Cust date value multiplier result
---- ---- ----- ---------- ------
A 2013-09-01 123.45 0.321 39.62
A 2013-09-02 123.45 0.234 28.88
A 2013-09-03 123.45 0.432 53.33
B 2013-09-03 543.21 0.432 234.66
B 2013-09-04 543.21 0.345 187.40
In SQL terms would want to join where multiplier-date>=from-date AND multiplier-date<=to-date.
One way of doing it is as below
index=myindex sourcetype=scCustomer |eval joinfield=1
| join type=outer max=0 joinfield [search index=myindex sourcetype=scMultiplier |eval joinfield=1 ]
| table Cust, from_date, to_date,value, multiplier_date,multiplier
| eval shouldInclude=case(multiplier_date >=from_date AND multiplier_date <= to_date,"Y",1=1, "N")
| search shouldInclude="Y" | eval result=value*multiplier|rename multiplier_date as date
| table Cust, date, value, multiplier, result | sort Cust,date
Please note that field name is updated to use underscore.
Instead of splitting Customer rows into multiple lines for range from_date to to_date, I am doing join from the Multiplier events. you should get one record for each day between range from_date to to_date.[limitation will be that the Muliplier events should have data for all the date ranges present in Customer]
How does this expand out the from->to dates ?
i.e
From To
A 2013-09-01 2013-09-03 123.45
Needs to become 3 separate lines.
Thats some pretty ingenious manipulation going on there though. Impressive.
off the top of my head -
1) write a macro using gentimes to evaluate the date ranges
2) mvexpand
3) join on the multiplier
4) profit
This problem is not well-suited to Splunk. However, it would be easier if you created a lookup table for the multipliers - this is not event data.
Second, it would be a trivial problem if the CustomerEvent sourcetype actually had one event per day. This data has clearly already been summarized from something else...
Unless you do at least one of these things, I cannot figure out a solution...