Splunk Search
Highlighted

Function for today's month/day/year?

Engager

I am writing a search query that looks for hosts that have appeared for the first time today and their count.

Here is what I was thinking:

< my search > | stats count last(date_year) AS first_year last(date_month) AS first_month last(date_mday) AS first_day BY host | where first_year=today's year AND first_month=today's month AND first_day=today's day

Is there a function in Splunk that returns today's year, month and day that would make the above search query work?

Tags (1)
Highlighted

Re: Function for today's month/day/year?

Legend

You can use the functions eval and strftime. Either pull out the year, month and day separately:

< your search > | eval todaysyear=strftime(now(),"%Y") | eval todaysmonth=strftime(now(),"%m") | eval todaysday=strftime(now(),"%d") | stats count last(date_year) AS first_year last(date_month) AS first_month last(date_mday) AS first_day BY host | where first_year=todaysyear AND first_month=todaysmonth AND first_day=todaysday

Or concatenate them together if you want less evals:

< your search > | eval todaysdate=strftime(now(),"%Y%m%d") | stats count last(date_year) AS first_year last(date_month) AS first_month last(date_mday) AS first_day BY host | where first_year.first_month.first_day=todaysdate
Highlighted

Re: Function for today's month/day/year?

Splunk Employee
Splunk Employee

This works, but really it's probably easier to just do the math directly using the epoch time, which is just seconds. All you need is to compare the time of appearance of the host with relative_time(now(),"@d"). If it's less, then it's old, if it's greater, then it's new. No need for all the string conversions.

0 Karma
Highlighted

Re: Function for today's month/day/year?

Splunk Employee
Splunk Employee

Probably simpler to use:

... | stats min(_time) as firsttime by host | where firsttime > relative_time(now(),"@d")

View solution in original post

Highlighted

Re: Function for today's month/day/year?

Legend

Excellent idea. Didn't even know about that function 🙂

0 Karma
Highlighted

Re: Function for today's month/day/year?

I think this might be helpful.

=DATE(2015, 5, 20) - returns a serial number corresponding to 20-May-2015.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month.

=DATE(2015, 5, 20)-5 - subtracts 5 days from May 20, 2015.

For additionally you can visit here.

0 Karma