topic Re: How to edit my search to extract a single record from an inputlookup file? in Splunk Search

How to edit my search to extract a single record from an inputlookup file?

rvoninski_splun — Sat, 25 Feb 2017 23:41:41 GMT

I have an inputlookup file that shows temperature records and is formatted as follows

rec-date,average-low,average-high,record-low,record-high,average-precip,average-snow
1-Jan,16,45,-18 (1974),69 (1997),"0.02""",NA
2-Jan,16,45,-12 (1974),67 (1997),"0.02""",NA
3-Jan,16,44,-18 (1974),76 (1997),"0.02""",NA
4-Jan,15,44,-22 (1974),68 (1956),"0.02""",NA
5-Jan,15,44,-15 (1971),70 (2001),"0.02""",NA
6-Jan,15,44,-20 (1974),70 (2001),"0.02""",NA
7-Jan,15,44,-11 (1970),61 (1990),"0.02""",NA
8-Jan,15,44,-12 (1970),67 (1956),"0.02""",NA
9-Jan,15,44,-20 (1962),72 (2003),"0.02""",NA
10-Jan,15,45,-29 (1962),65 (1990),"0.02""",NA
11-Jan,15,45,-22 (1963),72 (1990),"0.02""",NA
12-Jan,15,45,-32 (1963),70 (1953),"0.02""",NA
13-Jan,16,45,-18 (1963),68 (1996),"0.02""",NA
14-Jan,16,45,-10 (1972),73 (1996),"0.02""",NA
15-Jan,16,45,-9 (1972),64 (1996),"0.02""",NA
16-Jan,16,45,-10 (1992),68 (1996),"0.02""",NA
17-Jan,16,45,-9 (1962),65 (1965),"0.02""",NA
18-Jan,16,45,-21 (1984),66 (1985),"0.02""",NA
19-Jan,16,45,-18 (1963),69 (1985),"0.01""",NA
20-Jan,16,45,-17 (1962),70 (1986),"0.01""",NA
21-Jan,16,45,-16 (1959),70 (1986),"0.01""",NA
22-Jan,16,45,-12 (1962),64 (1969),"0.01""",NA
23-Jan,16,45,-16 (1963),66 (1981),"0.01""",NA
24-Jan,16,45,-8 (1963),69 (1981),"0.01""",NA
25-Jan,16,46,-1 (1957),68 (1981),"0.01""",NA
26-Jan,17,46,-10 (1996),64 (1953),"0.01""",NA
27-Jan,17,46,-13 (1963),72 (1975),"0.01""",NA
28-Jan,17,46,-7 (1980),69 (2003),"0.01""",NA
29-Jan,17,46,-5 (1957),65 (1987),"0.01""",NA
30-Jan,17,46,-4 (1994),65 (1976),"0.01""",NA
31-Jan,17,46,-9 (1994),69 (1954),"0.01""",NA

I would like to extract the records for just the current date and am trying something like this

|inputlookup Historic-80014-temps.csv | eval todaysDateString=strftime(now()," %e-%b") | where rec-date==todaysDateString

This returns nothing and I have verified that todaysDateString is coming in the same format as the rec-date below. Can someone help me build a search that will work for this?

BONUS points: I am also trying to pull the records for just the current month also. Attached is a screenshot of how the data is looking inside of Splunk.

Thank you.

Re: How to edit my search to extract a single record from an inputlookup file?

somesoni2 — Sun, 26 Feb 2017 03:57:59 GMT

I guess you just need a minor fix. Since your field names in lookup contains hyphen, you need to enclose them in single quotes to user the field value.
Updated

|inputlookup Historic-80014-temps.csv | eval todaysDateString=strftime(now(),"%e-%b") | where trim('rec-date')=todaysDateString

To get the data only for current month, try like this (% is the wildcard in like command.

|inputlookup Historic-80014-temps.csv | eval todaysDateString=strftime(now(),"%b") | where like('rec-date',"%".todaysDateString)

Re: How to edit my search to extract a single record from an inputlookup file?

gvmorley — Sun, 26 Feb 2017 04:37:43 GMT

Hi,

There's a couple of things you might like to try here.

Firstly, I think you want to add a 'year' to your dates, so that you can use them with strptime.

Then you'll want to have a bit fun converting things into epoch time.

Here's an example of what you could do:

| from inputlookup:"temp-test.csv"
| eval rec-date='rec-date'."-2017"
| eval rec_date_epoch=strptime('rec-date',"%-d-%b-%Y")
| eval rec_date_local=strftime(rec_date_epoch,"%d/%m/%Y %H:%M:%S")
| eval now_local=strftime(now(),"%d/%m/%Y")." 00:00:00"
| eval now_epoch=strptime(now_local,"%d/%m/%Y %H:%M:%S")
| eval tomorrow_epoch=now_epoch+86400
| eval tomorrow_local=strftime(tomorrow_epoch,"%d/%m/%Y %H:%M:%S")
| where (rec_date_epoch >= now_epoch) AND (rec_date_epoch < tomorrow_epoch)

It's probably more work than you actually need, but should help see the steps along the way.

One thing to note (and don't ask me why), I've found that fields with hyphens (such as your rec-date) often need to be enclosed in apostrophes when used with eval.

Regarding the 'month' question, you'll need to work out the epoch times for the 1st of each month. This is less easy due to the variable nature of the number of days (and therefore seconds) in each month.

I'd probably do something like pre-calculate a lookup table with these in for the year and then reference that.

You could start with this:

| makeresults
| fields - _time
| eval month=mvappend("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
| mvexpand month
| eval date="1-".month."-2017 00:00:00"
| eval date_epoch=strptime(date,"%-d-%b-%Y %H:%M:%S")

Using this, you could do the whole thing 'inline' but you'll always be creating this same dataset.

So, don't do this, but here's an example:

| from inputlookup:"temp-test.csv" 
| eval rec-date='rec-date'."-2017" 
| eval rec_date_epoch=strptime('rec-date',"%-d-%b-%Y") 
| eval rec_date_local=strftime(rec_date_epoch,"%d/%m/%Y %H:%M:%S") 
| eval now_local=strftime(now(),"%d/%m/%Y")." 00:00:00" 
| eval now_epoch=strptime(now_local,"%d/%m/%Y %H:%M:%S") 
| eval tomorrow_epoch=now_epoch+86400 
| eval tomorrow_local=strftime(tomorrow_epoch,"%d/%m/%Y %H:%M:%S") 
| join * [
| makeresults
| fields - _time
| eval month=mvappend("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
| mvexpand month
| eval date="1-".month."-2017 00:00:00"
| eval date_epoch=strptime(date,"%-d-%b-%Y %H:%M:%S")
| eval now_month_num=strftime(now(),"%-m")
| eval next_month_num=now_month_num+1
| eval now_month_local="1/".now_month_num."/2017 00:00:00"
| eval next_month_local="1/".next_month_num."/2017 00:00:00"
| eval now_month_epoch=strptime(now_month_local,"%-d/%-m/%Y %H:%M:%S")
| eval next_month_epoch=strptime(next_month_local,"%-d/%-m/%Y %H:%M:%S")
| fields now_month_epoch,next_month_epoch
| dedup now_month_epoch]
| where (rec_date_epoch >= now_month_epoch) AND (rec_date_epoch < next_month_epoch)

IMPORTANT: This does not deal with the roll-over of the year!

Hopefully this give you a few ideas on what you could try.

Re: How to edit my search to extract a single record from an inputlookup file?

rvoninski_splun — Sun, 26 Feb 2017 21:33:10 GMT

Weird behavior that I am hoping you might have insight to? The 1st set above still returns nothing. But the second one where we want the entire month works perfectly. Ideas?

Re: How to edit my search to extract a single record from an inputlookup file?

rvoninski_splun — Sun, 26 Feb 2017 21:35:24 GMT

Interesting approach. I need to play with this code. You have given me some ideas on directions I wasnt even thinking of going. Very impressive. Thank you.

Re: How to edit my search to extract a single record from an inputlookup file?

rvoninski_splun — Sun, 26 Feb 2017 21:50:43 GMT

I finally went with something kinda ugly but it works. If I break it up with REX and then do a comparison it seems to work.

 |inputlookup Historic-80014-temps.csv | rename rec-date AS recDate | eval todaysMonthString=strftime(now(),"%b") | eval todaysDateString=strftime(now(),"%e") | rex field=recDate "(?<testDate>.+?)-" | rex field=recDate ".+?-(?<testMonth>.+?)$"|where testMonth==todaysMonthString AND testDate==todaysDateString

Re: How to edit my search to extract a single record from an inputlookup file?

somesoni2 — Sun, 26 Feb 2017 22:48:08 GMT

This would work too. A little improved version of yours:

|inputlookup Historic-80014-temps.csv  | eval todaysMonthString=strftime(now(),"%b") | eval todaysDateString=strftime(now(),"%e") | rex field="rec-date" "(?<testDate>.+?)-<testMonth>.+?)" | where testMonth==todaysMonthString AND testDate==todaysDateString

Also, see if my updates answer works for you now.