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?
Probably simpler to use:
... | stats min(_time) as firsttime by host | where firsttime > relative_time(now(),"@d")
I think that this could help.
=DATE(2023, 6, 21) - returns a serial number corresponding to 21-June-2023.
=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.
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.
Probably simpler to use:
... | stats min(_time) as firsttime by host | where firsttime > relative_time(now(),"@d")
Excellent idea. Didn't even know about that function 🙂
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 eval
s:
< 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
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.