Splunk - timestamp conversion for dashboard

[dhanasekarjanak]

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
[email protected] 03/31/2018
[email protected] 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

[dhanasekarjanak]

Thank you so much! it worked.

Dhana

[elliotproebstel · Accepted solution]

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.