Hi, I have a lookup table which has the list of email id's and the dates they logged into the application as below,
Username time
testuser1@gmail.com 03/31/2018
testuser2@gmail.com 03/30/2018
if I use the date range on the Splunk dashboard, the query is working without any issue, because it shows the timestamp on the query, but if I choose the present timeframe such as last 1 day, last 30 minutes, it is populating as now and -15m, I'm unable to convert the time format, Could anyone please help me to resolve the issue?
Query Format -
| eval mytime1=strftime($time.latest$,"%Y%m%d")
| eval mytime2=strftime($time.earliest$,"%Y%m%d")
| where mytime <= mytime1 and mytime >= mytime2
Present time frame-
| eval mytime1=strftime(now,"%Y%m%d")
| eval mytime2=strftime(-15m,"%Y%m%d")
| where mytime <= mytime1 and mytime >= mytime2
Date range -
| eval mytime1=strftime(1516942800,"%Y%m%d")
| eval mytime2=strftime(1514782800,"%Y%m%d")
| where mytime <= mytime1 and mytime >= mytime2
Thanks,
Dhana
When I have date stamps in my lookup files and want to filter results based on date, I don't use the time tokens, because there are so many variations and edge cases. Instead, I do this:
| addinfo
| where info_min_time<=whatever_start_date_time AND info_max_time>=whatever_end_time
| fields - info_*
The addinfo
command will add four fields to every event - info_max_time
(equivalent to the $latest$ token, but always in epoch format), info_min_time
(equivalent to the $earliest$ token, but always in epoch format), info_search_time
(when the search was run), and info_sid
(a unique ID assigned to the search).
The line | fields - info_*
is just to remove those fields after I've used them for comparisons.
Thank you so much! it worked.
Dhana
When I have date stamps in my lookup files and want to filter results based on date, I don't use the time tokens, because there are so many variations and edge cases. Instead, I do this:
| addinfo
| where info_min_time<=whatever_start_date_time AND info_max_time>=whatever_end_time
| fields - info_*
The addinfo
command will add four fields to every event - info_max_time
(equivalent to the $latest$ token, but always in epoch format), info_min_time
(equivalent to the $earliest$ token, but always in epoch format), info_search_time
(when the search was run), and info_sid
(a unique ID assigned to the search).
The line | fields - info_*
is just to remove those fields after I've used them for comparisons.