Date range to filter from lookup table past 30 days

Follow

Shajedul Alam

• June 25, 2021 23:32
• Edited

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!

0

• 

  Jorge Silva

  • June 30, 2021 20:15

  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.

  0

  Comment actions Permalink
• 

  Shajedul Alam

  • July 07, 2021 00:18

  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

   

   

   

  0

  Comment actions Permalink

Please sign in to leave a comment.