Date range to filter from lookup table past 30 days
Hi!
I have created a date (yyyy/mm/dd) column called "Initialdatesigned" in my lookup table. This table also contains the account name column along with the initialdatesigned. I would like to filter my query to look for accounts in the lookuptable where it only displays accounts signed within the last 30 days.
I tried to search in sumologic articles but was unable to find the right solution.
Example:
| Lookup accountname,initialdatesigned from path: xyz on account=accountname
| where initialdatesigned < (initialdatesigned,-30d) //the goal
Any help will be highly appreciated!
Thanks!
-
I think it is best if you convert the date into milliseconds and then do your calculations.
For example, to covert the field "initialdatesigned" to milliseconds you would use the "parseDate" operator, something like this:
| parseDate(initialdatesigned, "yyyy/MM/dd") as initialdatesigned_ms
Then, knowing that 2592000000 milliseconds equals 30 days, you could do:
| now() as current_date (this gets the current time or you can also use _messagetime to use the time of the message instead of the current time)
| (current_date-2592000000) as ThirtyDaysAgo
| where initialdatesigned_ms > ThirtyDaysAgo
For more information about the parseDate operator, please go to https://help.sumologic.com/05Search/Search-Query-Language/01-Parse-Operators/parseDate
For the now operator, please go to https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/now
Give this a try and if you have any further questions, then please open a ticket with support by emailing us at support@sumologic.com.
-
Thank you, Jorge. I am still running into some hiccups. I Will be sending an email to support@sumologic.com
My CSV file has a date column in format yyyy-MM-dd
When I try to use the parsedate function using the statement below
| parseDate(initialdatesigned, "yyyy-MM-dd'T'HH:mm:ss.SSSXXX") as milliseconds //converting to ms
I get an error
Unparseable date: "2021-07-02"Is there another way to convert to milliseconds?Thanks
Please sign in to leave a comment.
Comments
2 comments