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