All Apps and Add-ons

how to get the time from a field

hqw
Path Finder

Hi all,

I have a saved search which is directly extracted from the database, the date formate is like below: 13:02:01 2016-03-28, now i want to extract the date and only display those records for yesterday, but nothing happened when I create a new column with below search:

|savedsearch **
|eval day=strftime(date,"%Y-%m-%d")
|eval now=strftime(now(), "%Y-%m-%d")
|eval time_interval=now-day
|where time_interval="1"

The problems is that "day" field can't be created using this, it seems that strftime function can't works on saved search or something. I also tried another search to extract date, but it also failed, "complete_day" could be created and display as "2016-03-28" for instance, but when I create time_interval, it can't be calculated as a number.

|savedsearch rt_visits
|eval temp=split(date," ")
| eval complete_day=mvindex(temp,1)
|eval nowstring=strftime(now(), "%Y-%m-%d")
|eval time_interval=nowstring-complete_day

Can anyone help me on this question? Thanks a lot.

1 Solution

woodcock
Esteemed Legend

You can use strftime in any search.
The problem with now is that it is already in epoch.
The problem with day is that date is not in epoch so you either need to convert to epoch or use string functions; try this instead
The problem with your search is that you are not creating epoch fields (using the wrong function) so your subtraction cannot work.
I assume that are try to check if the date on the event is not "today". If so, Try this (note that time_interval is in seconds and 1 day has 60*60*24 seconds).

| savedsearch **
| eval day=strptime(date,"%H:%M:%S %Y-%m-%d") | bucket date span=1d
| eval now=now() | bucket now span=1d
| eval time_interval = now - day
| search time_interval>0

View solution in original post

woodcock
Esteemed Legend

You can use strftime in any search.
The problem with now is that it is already in epoch.
The problem with day is that date is not in epoch so you either need to convert to epoch or use string functions; try this instead
The problem with your search is that you are not creating epoch fields (using the wrong function) so your subtraction cannot work.
I assume that are try to check if the date on the event is not "today". If so, Try this (note that time_interval is in seconds and 1 day has 60*60*24 seconds).

| savedsearch **
| eval day=strptime(date,"%H:%M:%S %Y-%m-%d") | bucket date span=1d
| eval now=now() | bucket now span=1d
| eval time_interval = now - day
| search time_interval>0

hqw
Path Finder

Dear all

Issue has been fixed by below command, Thanks a lot for all your help.
| savedsearch **
| eval date = strptime(date, "%H:%M:%S %Y-%m-%d")
| where date<= relative_time(now(),"@d") AND (date>= relative_time(now(),"-1d@d"))

0 Karma

hqw
Path Finder

Hi Woodcock,

Thanks again for your help, with your search, i can both get now and day column as unix epoch, and result is displayed like below:
day time_interval
1459141321.000000 101713.232931

so how can I change time_interval as a readable formate like the difference days between current day and the recode day (in the date column)? since I only want to select yesterday's result, and it is very hard for me to filter.

Thanks

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>