How to search for an event from last week based on time value field?

[vnguyen46]

Hi - I am searching for events based on time field Last_Login_Time (sample value: 2019-06-13T20:26:12.000Z) which happened in last one week.
In SQL, it can be Last_Login_Time > getdate() - 7. How it works in Splunk?

[harsmarvania57 · Accepted solution]

Hi,

Please try below query.

<yourBaseSearch>
| eval llt_epoch = strptime(Last_login_time, "%Y-%m-%dT%H:%M:%S.%3Q")
| where llt_epoch > now()-604800

[vnguyen46]

Works perfectly. Script by VatsalJagani should work as well. Thank you!

[VatsalJagani]

Hello @vnguyen46,

You can write say the similar thing in Splunk also. | where Last_Login_Time > relative_time(now(),"-7d")

Make sure your field Last_Login_Time is in epoch format (integer). If it is not in epoch format, instead it is in string then you can convert to epoch format like: | eval Last_Login_Time=strptime(Last_Login_Time,"%Y-%m-%dT%H:%M:%S.%3Q").

In comparison SQL, you need to run this search in "All Time". But in Splunk if you know that some event for which Last Login Time is in last 7 days event can be in Last 10 days not before that, then you can reduce the timerange to Last 10 Days to help Splunk complete search quicker. Or if you know value we are finding for event cannot be before last 7 days then you can say timerange to Last 7 Days.

Hope this helps!!!