Splunk Search

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

rvoninski_splun
Splunk Employee
Splunk Employee

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.

alt textThank you.

Tags (2)
0 Karma

gvmorley
Contributor

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.

0 Karma

rvoninski_splun
Splunk Employee
Splunk Employee

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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)
0 Karma

rvoninski_splun
Splunk Employee
Splunk Employee

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?

0 Karma

rvoninski_splun
Splunk Employee
Splunk Employee

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...