Sub-search Where a>=b AND a<=c

[Jodge]

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.

[somesoni2]

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.

[somesoni2]

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]

[jonuwz]

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.

[jonuwz]

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

[lguinn2]

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