topic Re: Filtering data from lookup csv file based on time difference in Getting Data In

Filtering data from lookup csv file based on time difference

sambit_kabi — Wed, 30 Sep 2020 04:11:46 GMT

I have a lookup csv file which has the following data.
Day Messages
12/02/2020 1571
12/02/2020 302
12/02/2020 1

What I want to do is read the Day column and then subtract the day from today's date to check if the difference is greater than 30.
If the diff > 30 filter it out.

I tried the following query and it doesn't work.

| inputlookup messages_per_day.csv | eval today=strftime(now(), "%d/%m/%Y") | eval diff=today - Day

Re: Filtering data from lookup csv file based on time difference

richgalloway — Thu, 13 Feb 2020 13:51:10 GMT

Timestamps cannot be compared in string form. Try the inverse of your query.

| inputlookup messages_per_day.csv | eval today=strptime(Day, "%d/%m/%Y") | eval diff=now() - today

Re: Filtering data from lookup csv file based on time difference

spayneort — Fri, 14 Feb 2020 07:13:07 GMT

| inputlookup messages_per_day.csv 
| where relative_time(now(),"-30d@d") < strptime(Day, "%d/%m/%Y")

Re: Filtering data from lookup csv file based on time difference

sambit_kabi — Sat, 15 Feb 2020 06:40:56 GMT

Thanks for the help. Unfortunately the strptime function didn't work for me. It didn't convert the time in csv to time to be compared. I don't get result for today variable.

However I managed to find a solution by dumping the time in "secs from epoch" in the csv and then compared the diff between the today's start time with the time in csv.

used constructs like | eval diff=relative_time(now(), "@d") - Day | where diff < 86400.

Still wondering why strptime didn't work for me. However I found that | convert ctime(Day) did work for me.

Re: Filtering data from lookup csv file based on time difference

sambit_kabi — Sat, 15 Feb 2020 06:43:49 GMT

Thanks for the help. I used the relative_time function as you pointed out. That came in handy.
Unfortunately the strptime function didn't work for me as I have mentioned before. It didn't convert the time in csv to time to be compared. I don't get result for today variable.

However I managed to find a solution by dumping the time in "secs from epoch" in the csv and then compared the diff between the today's start time with the time in csv.

used constructs like | eval diff=relative_time(now(), "@d") - Day | where diff < 86400.

Still wondering why strptime didn't work for me. However I found that | convert ctime(Day) did work for me

Is there any condition that strptime has when used with inputlookup. Nothing mentioned in the documentation.

Re: Filtering data from lookup csv file based on time difference

sambit_kabi — Sat, 15 Feb 2020 06:51:18 GMT

I managed to solve this in the following manner. Though it may not be efficient but it worked for me.

Instead of the date I kept the time as "seconds from epoch" in the csv file which looked like(assume the data is dummy).

Day Messages
12022020 1571
12022020 302
12022020 1

Then using the following construct i was able to take a diff and compare it.
| inputlookup abc.csv | eval today=relative_time(now(), "%d") | eval diff = today - Day | where diff <= 86400

I am sure the strptime function should have done the job too with my previous approach but somehow in my case it didn't convert the date in the csv to date that can be compared. So I took this approach. Still trying to figure out why strptime didn't work.