STRPTIME date question - Conf19

[macattck]

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

[arjunpkishore5 · Accepted solution]

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

[macattck]

thanks, your #3 and #4 step worked.

[arjunpkishore5]

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.

[macattck]

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?

[woodcock]

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 

[macattck]

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.

[woodcock]

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.

[macattck]

Sorry about the way my question was phrased. It's my first post ever. Let me try to slow down and be more descriptive.

1. The lastLoginDateStr is the data column in question
2. My file has two years worth of data up to the last day of the month I run it for, in this case 9/30/2019. The last day of the month will always appear e.g. 10/31/2019 will show up when I run the file on 11/01/2019
3. If I know my Max date for the entire file is 9/30/2019, I want to pull in all records from 9/01/2019 to meet my 30 day data set. This is why it's hard coded.
4. I would like to create a Reference data field that takes the Max Date (9/30/2019) and subtract 30 days. I would then like to say WHERE lastLoginDate >= Reference Date (9/30/2019 - 30 days = 9/01/2019)

| eval lastLoginDate=strptime(lastLoginDateStr, "%m/%d/%Y")
| eval referenceDate=strptime("9/1/2019", "%m/%d/%Y")
| where lastLoginDate>=referenceDate

Thank you!