How do I write a search to compare timestamps in indexed data to timestamps in a lookup table?

[splunk_hvijay]

Need a help urgently in using a lookup in a search. I have a lookup table as below and need to use this data in the search that I have indexed. Kindly help me.

Effective Date  Description Value
1/1/2015          Image       1
1/1/2015          Print       2
1/1/2015          Presort       3
1/1/2016          Image       5
1/1/2016          Print       15
1/1/2016          Presort       20

I already indexed the data from a database and I have a timestamp column (10/15/2016 12:00:00 AM). I wanted to compare this timestamp with the lookup table timestamp like Timestamp> Effective date and if Description = Image/Post/Return etc , then the output should be the "Value" from lookup table.

Already Indexed on Splunk

Business Unit     Timestamp       
Finance           10/12/2016
HR                10/15/2016 

Expected Output

Business Unit     Timestamp      Image Cost     Print Cost     Presort Cost
Finance           10/12/2016     5              15             20
HR                10/15/2016     5              15             20
Other             10/15/2015     1              2              3

Basically I want to compare:
1. TimeStamp (Already indexed from DB) and Effective date from Lookup
2. Image Cost (Need to eval the column) and Description from Lookup

and provide the output as below. Please help.

[bshuler_splunk]

I created the lookup using:

| makeresults | eval data="#1/1/2015%Image%1#1/1/2015%Print%2#1/1/2015%Presort%3#1/1/2016%Image%5#1/1/2016%Print%15#1/1/2016%Presort%20" | rex max_match=0 field=data "(?<line>[^#]+)"  | mvexpand line | rex field=line "(?<Effective_Date>[^%]+)%(?<Description>[^%]+)%(?<Value>[^%]+)" | rename Effective_Date as "Effective Date" | table "Effective Date"    Description    Value | outputlookup cost_lookup.csv

Then I simulate your indexed data with this query (I changed the dates so they would match the lookup):

| makeresults | eval data="Finance%1/1/2016#HR%1/1/2015" | rex max_match=0 field=data "(?<line>[^#]+)"  | mvexpand line | rex field=line "(?<Business_Unit>[^%]+)%(?<Timestamp>[^%]+)" | rename Business_Unit as "Business Unit" | table "Business Unit" Timestamp

Next, I bring in the lookup data:

| makeresults | eval data="Finance%1/1/2016#HR%1/1/2015" | rex max_match=0 field=data "(?<line>[^#]+)"  | mvexpand line | rex field=line "(?<Business_Unit>[^%]+)%(?<Timestamp>[^%]+)" | rename Business_Unit as "Business Unit" | table "Business Unit" Timestamp | lookup cost_lookup.csv "Effective Date" as Timestamp

Finally, I use evals to create the 3 fields you need. The evals use mvfind to find the index in the multivalue field, and mvindex to return the correct value.

| makeresults | eval data="Finance%1/1/2016#HR%1/1/2015" | rex max_match=0 field=data "(?<line>[^#]+)"  | mvexpand line | rex field=line "(?<Business_Unit>[^%]+)%(?<Timestamp>[^%]+)" | rename Business_Unit as "Business Unit" | table "Business Unit" Timestamp | lookup cost_lookup.csv "Effective Date" as Timestamp | eval "Image Cost"=mvindex(Value, mvfind(Description, "Image")) | eval "Print Cost"=mvindex(Value, mvfind(Description, "Print")) | eval "Presort Cost"=mvindex(Value, mvfind(Description, "Presort")) | table "Business Unit"     Timestamp      "Image Cost"     "Print Cost"     "Presort Cost"

[pwmcintyre]

Does the join command help you here? say if you join on Timestamp?

http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Join