Using data in a lookup table as part of a search

[richhart_1963]

I'm trying to use a lookup table to find records in my database, but I'm not having much luck. It may just be that I'm asking too much of Splunk.

My lookup table consists of 4 fields: mrch_num, term_num, start_time, and end_time. mrch_num and term_num are straight-up fields from my database. start_time and end_time are value I want to compare in records that match the mrch_num and term_num. My query so far looks like this:

`search` [|inputlookup key_requests.csv | table mrch_num,term_num,$start_time,$end_time] j_timestamp>=start_time j_timestamp<=end_time  | `transtuff`

The object is to find transactions with mrch_num=mrch_num, term_num=term_num and j_timestamp between the values from start_time and end_time.

I don't think I'm properly passing the start_time and end_time from my lookup table to the search.

Can anyone give me some pointers?

Thanks,

Rich

[gcusello · Accepted solution]

Hi @richhart_1963,

I'm not sure to have understood your need because the macro doesn't help understanding.

Anyway I think that you should use the lookup command instead the inputlookup.

In this way you can associate to the events, for each  mrch_num and term_num, the start_time and end_time from the lookup, so you can use them for the conditions you need.

Something like this:

`search` 
| lookup key_requests.csv mrch_num term_num OUTPUT start_time end_time
| eval j_timestamp_et=strptime(j_timestamp,"its format", start_time_et=strptime(start_time,"its format", end_time_et=strptime(end_time,"its format",
| search j_timestamp_et>=start_time_et AND j_timestamp_et<=end_time_et
| `transtuff`

To compare dates, remember to transform all dates in epochtime using the strptime funcion (I don't know the format of your dates).

Ciao.

Giuseppe

[richhart_1963]

Thanks for your reply. It helped me get to my answer.

To address your questions, the macros I included are only left as macros because they have no affect on the outcome. They are just shorthands for the source_type nonsense that precedes a query and the common output fields. The timestamp is just a number, not a time representation at all, so I am just using it verbatim. What finally worked for me was a combination of inputlookup to narrow the search and lookup to get the start_time and end_time values "into" the database. Here is the final query. It returns all transactions that occurred between start_time and end_time on a particular mrch_num/term_num combo where the times are different for each mrch_num,term_num.

`search` [|inputlookup template.csv | fields mrch_num,term_num] | lookup template mrch_num ,term_num outputnew start_time,end_time | where j_timestamp>=start_time AND j_timestamp<=end_time | stats count,min(entrytime),max(entrytime) by mrch_num,term_num,acct_num

[gcusello]

Hi @richhart_1963,

good.

if this answer solves your need, please accept it for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉