topic Re: How to limit number of rows by searching for begin date > 12/31/2009 in Splunk Search

How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Tue, 24 Jun 2014 15:47:58 GMT

My basic search is : eventtype=FAS
Gives the following results:
RESP BEGIN DATE FISCAL YEAR PLACE
Yes 12/22/2009 2010 VT
Yes 11/18/2010 2011 VT
Yes 11/15/2012 2013 FL
Yes 8/7/2009 2009 VT
Yes 8/6/2009 2009 VT
Yes 5/20/2011 2011 VT
Yes 1/21/2009 2009 VT
Yes 1/11/2011 2011 FL
……

How can I limit my search using dates > than 8/31/2009?
The following gives unpredictable results:

eventtype=FAS " BEGIN DATE ">"12/31/2009"

Thanks!
JK

Re: How to limit number of rows by searching for begin date > 12/31/2009

lguinn2 — Tue, 24 Jun 2014 16:28:48 GMT

Splunk does not have a data type of date, so your search is looking at the date as a string. Try this, which assumes that there is actually a field named BEGIN DATE

eventtype=FAS 
| eval begin_date = strptime('BEGIN DATE',"%m/%d/%Y") 
| where begin_date > strptime("12/31/2009","%m/%d/%Y")

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Tue, 24 Jun 2014 16:41:03 GMT

You could extract the day, month, and year as individual fields and filter based on that. For >12/31/2009 you'd just filter by year>2009, for >8/31/2009 you'd filter by year>2009 OR (year=2009 month>8).

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Tue, 24 Jun 2014 16:41:06 GMT

Iguinn,

This will do. Thanks for your help!

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Tue, 24 Jun 2014 17:56:45 GMT

martin_mueller,

How do you extract the day, month and year as individual fields? Would you please provide an example?
Thanks,

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Tue, 24 Jun 2014 17:59:12 GMT

That depends on your raw data. Assuming it looks like this:

something begin_date=12/31/2009 something else

You could use this expression:

begin_date=(?<month>\d+)/(?<day>\d+)/(?<year>\d+)

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Tue, 24 Jun 2014 18:01:13 GMT

Iguinn,

What alternatives are there to using "eventtype" if I cannot create one with the results of this query?
Eventtype search string cannot be a search pipeline or contain a subsearch.
Thanks!
JK

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Tue, 24 Jun 2014 18:02:31 GMT

You could move those two commands into a macro to avoid typing the whole shebang into many searches.

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Tue, 24 Jun 2014 19:03:01 GMT

The raw data looks like this: BEGIN DATE = 12/31/2009
(please note the space between BEGIN DATE)

I tried the following and showed no syntax errors but did not get any results from the query. What am I missing?

eventtype=FAS
| eval 'BEGIN DATE'="(?\d+)/(?\d+)/(?\d+)"
| where year>2009 OR (year=2009 AND month>8)

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Tue, 24 Jun 2014 19:06:10 GMT

eval doesn't do field extraction. Either add a field extraction through Settings -> Fields -> Field extractions, or use rex in the search like this:

eventtype=FAS | rex "BEGIN DATE = (?<month>\d+)/(?<day>\d+)/(?<year>\d+)" | where year>2009 OR (year=2009 AND month>8)

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Mon, 28 Sep 2020 16:55:14 GMT

I tried:
eventtype=FAS | rex "BEGIN DATE = (?\d+)/(?\d+)/(?\d+)" | where year>2009 OR (year=2009 AND month>8) but could not get any results. I am still curious as to why it didn't work.

Then I took another route:

eventtype=FAS |eval begin_month = strftime(strptime('BEGIN DATE',"%m/%d/%Y") ,"%m")|eval begin_Year = strftime(strptime('BEGIN DATE',"%m/%d/%Y") ,"%Y")|where begin_Year > 2009 OR (begin_Year = 2009 AND begin_month>8)

Thank you for your comments!
JK

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Mon, 28 Sep 2020 16:55:17 GMT

Re: How to limit number of rows by searching for begin date > 12/31/2009

lguinn2 — Wed, 25 Jun 2014 00:43:40 GMT

In Splunk 6.1, you can create a calculated field named begin_date using the second line of the search above as a pattern. Once you have that field, your search would be

eventtype=FAS begin_date> 1262303999

Because 1262303999 is 12/31/2009 23:59:59 in epoch time. (I used a time converter to figure that out.)

This technique should allow you to save the resulting search as an eventtype

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Wed, 25 Jun 2014 07:04:44 GMT

Leave off the where and confirm that you have a year, month, and day field.

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Wed, 25 Jun 2014 14:31:43 GMT

Thanks! JK

Re: How to limit number of rows by searching for begin date > 12/31/2009

quanteq — Wed, 25 Jun 2014 15:14:38 GMT

I found the following:

Prior to 1972, this time was called Greenwich Mean Time (GMT) but is now referred to as Coordinated Universal Time or Universal Time Coordinated (UTC)

I found this link to do the conversion:
http://www.freeformatter.com/epoch-timestamp-to-date-converter.html

For GMT:
Thu, 31 Dec 2009 23:59:59
1262239200

For Local:
12/31/2009 23:59:59 gives
1262325599

How do you know which one to use?
The other question is, since I am getting a variable named: "BEGIN DATE", how do I create an alias or rename it to: "begin_date"?

Thanks!
JK

Re: How to limit number of rows by searching for begin date > 12/31/2009

martin_mueller — Wed, 25 Jun 2014 21:39:44 GMT

Just use Splunk to convert the time:

| stats count | eval timestamp = strptime("2010-01-01 00:00:00", "%F %T")

Should yield 1262300400.

As for aliases, take a look at Settings -> Fields -> Field Aliases.