The below SPL works. The lastLoginDate is a range of dates from 2018 through 9/30/2019. I would like to find the last 30 days or 1 month but I have to manually update the SPL with a hard date. If this was SQL, I would create the Max(lastLoginDate) minus 30 days but it's SPL. Help - thanks.
| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
| eval referenceDate=strptime("9/1/2019", "%m/%d/%Y")
| where lastLoginDate>=referenceDate
what you need is eventstats and relative_time
| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
| eval referenceDate=strptime("9/1/2019", "%m/%d/%Y")
| eventstats max(lastLoginDate) as referenceDate
| where lastLoginDate>=relative_time(referenceDate,"-30d")
If you have a set of key fields for which you want to do this use a by field in the eventstats as below:
| eventstats max(lastLoginDate) as referenceDate by key_field1, key_field2 ... key_fieldN
Please upvote and mark as answer if this helps
what you need is eventstats and relative_time
| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
| eval referenceDate=strptime("9/1/2019", "%m/%d/%Y")
| eventstats max(lastLoginDate) as referenceDate
| where lastLoginDate>=relative_time(referenceDate,"-30d")
If you have a set of key fields for which you want to do this use a by field in the eventstats as below:
| eventstats max(lastLoginDate) as referenceDate by key_field1, key_field2 ... key_fieldN
Please upvote and mark as answer if this helps
thanks, your #3 and #4 step worked.
Glad that I could help. 🙂 You can remove step 2. you don't need that since it gets overridden in step 3.
Could you please mark as answer if this is what you were looking for.
Thanks for responding. I should have added that the Max Date from the file is constant since it comes from an Excel File. That's why hard coding greater than "9/01/2019" works. I need to create a reference of the Max date from the entire file and then take out 1 month. Can you help with this?
Like this:
...| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
Followed by:
| where lastLoginDate >= relative_time(now(), "@m")
OR:
| where lastLoginDate >= relative_time(now(), "-30d@d")
OR just use the timepicker with:
| addinfo
| where lastLoginDate >= info_min_time AND lastLoginDate <= info_max_time
I cannot use a relative date or dynamic date. My data is static until it's refreshed once a month. I feel I need to create a reference of MaxDate from the entire file.
I have absolutely no idea what you mean if my answer does not make sense to you. You are going to have be FAR more descriptive about your need and post at least 2 examples of doing this on different hypothetical dates or files.
Sorry about the way my question was phrased. It's my first post ever. Let me try to slow down and be more descriptive.
| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
| eval referenceDate=strptime("9/1/2019", "%m/%d/%Y")
| where lastLoginDate>=referenceDate
Thank you!