Splunk Search
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Explorer

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

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

0 Karma
Highlighted

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

Communicator

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"
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.