topic Re: Convert string to date then filter by only dates within the last 48 hours in Getting Data In

Convert string to date then filter by only dates within the last 48 hours

JoshuaJohn — Mon, 05 Jun 2017 16:27:47 GMT

I have a variable that produces many strings but I need to convert them to a date value then filter by only the dates that are within the last 48 hours

I have this query so far but it isn't working, any ideas what I am doing incorrectly?

| inputlookup report.csv 
| rename u_last_policyrequest_time as "Last Policy Request" 
| search "Last Policy Request"=* 
| fields "Last Policy Request" 
| eval Last_Policy_Request = strptime("Last Policy Request", "%m/%d/%Y %H:%M") 
| where Last_Policy_Request < relative_time(now(),”-48h”)

Any ideas?

Re: Convert string to date then filter by only dates within the last 48 hours

richgalloway — Mon, 05 Jun 2017 16:34:39 GMT

Line #6 is selecting events that are more than 48 hours old. Try | where Last_Policy_Request > relative_time(now(),”-48h”).

Re: Convert string to date then filter by only dates within the last 48 hours

JoshuaJohn — Mon, 05 Jun 2017 16:48:50 GMT

Tried that with the last 200 days and didn't get any results (Should have provided all results) I am afraid I am not converting it to a date string correctly because even after removing %H:%M I still get results that look like this:

5/12/2017 15:22
5/17/2017 14:55
5/5/2017 20:04
5/17/2017 14:46
5/17/2017 15:01

Re: Convert string to date then filter by only dates within the last 48 hours

somesoni2 — Mon, 05 Jun 2017 16:52:21 GMT

Try this
Updated the where clause logic

| inputlookup report.csv | search u_last_policyrequest_time=*
| fields u_last_policyrequest_time
| eval Last_Policy_Request = strptime(u_last_policyrequest_time, "%m/%d/%Y %H:%M") 
 | where Last_Policy_Request > relative_time(now(),"-48h")

Re: Convert string to date then filter by only dates within the last 48 hours

richgalloway — Mon, 05 Jun 2017 17:11:55 GMT

It might help to see some sample data.

Re: Convert string to date then filter by only dates within the last 48 hours

JoshuaJohn — Mon, 05 Jun 2017 19:31:37 GMT

I am now getting all of the results:

I should be getting 0 of them.

5/12/2017 15:22 1494620520.000000
5/17/2017 14:55 1495050900.000000
5/5/2017 20:04 1494032640.000000
5/17/2017 14:46 1495050360.000000
5/17/2017 15:01 1495051260.000000
5/17/2017 14:38 1495049880.000000
5/15/2017 13:22 1494872520.000000
5/5/2017 15:50 1494017400.000000
5/17/2017 14:26 1495049160.000000
5/17/2017 15:01 1495051260.000000

Re: Convert string to date then filter by only dates within the last 48 hours

somesoni2 — Mon, 05 Jun 2017 19:34:10 GMT

Try the updated answer.

Re: Convert string to date then filter by only dates within the last 48 hours

JoshuaJohn — Mon, 05 Jun 2017 19:37:18 GMT

Awesome, thank you!