Timechart based on date and time string NOT the timestamp

[JWBailey]

I have data and time information in a log stored as a string. It is an additional field not the timestamp or _time.

How can I get a chart of these events based on this time information? For example, I have extracted this sting into a field named CloseDate. I need the count of events broken down into various windows: CloseDate < 30 days ago, CloseDate between 30 and 60 days ago, CloseDate between 60 and 90 days ago, and Close_Date>90 days ago.

Close_Date has no correlation with the event timestamp.

What is the most efficient way to do this?

Thank you,

[JWBailey · Accepted solution]

Thank you for the response. I also found another way to do this. Here is the solution I used:

1. I use strptime to convert CloseDate to epoc time. `[ ...| eval CD=strptime(CloseDate, "%Y-%m-%d %H:%M:%S") | ... ]`
2. I then use relativetime to calculate the epoc time of my benchmarks, 30 days, 60 days, and 90 days ago. `[ ... | eval days30=relativetime(now(),"-30d@d") | eval days60=relativetime(now(),"-60d@d") | eval days90=relativetime(now(),"-90d@d") | ... ]`
3. Use a nested if statement to calculate which window each event falls into. [ ... | eval Age=if(CDdays90,60,if(CDdays60,30,0))) | ... ]
4. Get rid of the events that were closed in the last 30 days (different than my initial question... I know..). [ ... | where Age>1 | ... ]
5. Display the results using stats. [ ... | stats count by Age ]

So, putting it all together:

::my search here::  | eval CD=strptime(Close_Date, "%Y-%m-%d %H:%M:%S") | eval days30=relative_time(now(),"-30d@d") | eval days60=relative_time(now(),"-60d@d") | eval days90=relative_time(now(),"-90d@d") | eval Age=if(CD<days90,90,if(CD<days60 AND CD>days90,60,if(CD<days30 AND CD>days60,30,0))) | where Age>1 | stats count by Age

[stephanefotso]

Hello JWBiley. Your problem was not easy, but i think, i found the solution. Let's go

Here is the situation:
In your events, you have a field called Close_Date, containing dates in the format 2015-05-20 09:15:30 (year-month-day hour:min:sec), and you want for example a search that will return events, there is 30 days. To accomplish your work, you need an initial date. For example, if Close_Date is populated like this in your events:

Close_Date
2015-05-20 8:15:45
2015-04-21 9:15:00
2015-03-22 9:15:55
2015-04-21 9:15:30
2015-04-22 14:15:45
2015-04-21 15:30:30
2015-04-21 8:15:45
2015-04-21 8:15:45
2015-04-21 8:15:45

By taking the last event's date (here, the first row) as your initial date, your search will have to return the first 3 events, because from 2015-05-20 8:15:45 to 2015-04-21 8:15:45, there is exactly 30 days.

Algorithm

1. if your initial date is for example 2015-05-31 10:30:20, target events there is 30 days, are events with 2015-05-02 10:30:20 as a Close_Date
2. if your initial date is for example 2015-05-30 10:30:20, target events there is 30 days, are events with 2015-05-01 10:30:20 as a Close_Date
3. if your initial date is for example Y-M-D 10:30:20 and the month is not january, with D<30, target events there is 30 days, are events with Y-(M-1)-(D+1) 10:30:20 as a Close_Date

4 . if your initial date is for example Y-01-D 10:30:20 and the month is january, with D<30, target events there is 30 days, are events with (Y-1)-12-(D+1) 10:30:20 as a Close_Date

Here is the implementation

1. Retrive the last value of CloseDate, and take it as the initial date. Let's call it **initialdate**
2. Extract the year, month, day, hour, minutes, and seconds from initial_date
3. Start comparisions - case we are the 31 of the month - case we are the 30 of the month - case the day is between 1 and 29 and the month is not january - case the day is between 1 and 29 and the month is january
4. evaluate the targetCloseDate

Here is the query:

...|eventstats first(Close_Date) as initial_date|rex field=initial_date "^(?P<year>\d+)\-(?<month>\d+)\-(?<day>\d+)\s(?<hour>\d+)\:(?<min>\d+)\:(?<sec>\d+)"|eval target_day31=case(day=31,02)|eval target_day30=case(day=30,01)|eval target_day=case(day<30,day+1)|eval target_day=if(target_day<10,"0"+target_day,target_day)|eval target_month_day=month-01|eval target_month_day=if(target_month_day<10,"0"+target_month_day,target_month_day)|eval january_target_day=case(month=01,12)|eval january_target_year=year-1|eval target_Close_Date=case(day=31,year+"-"+month+"-"+target_day31+" "+hour+":"+min+":"+sec,day=30,year+"-"+month+"-"+target_day30+" "+hour+":"+min+":"+sec,day<30 AND month!=1,year+"-"+target_month_day+"-"+target_day+" "+hour+":"+min+":"+sec,day<30 AND month=1,january_target_year+"-"+january_target_day+"-"+target_day+" "+hour+":"+min+":"+sec)|where Close_Date=target_Close_Date|stats count by target_Close_Date

Thanks.

[JWBailey]

Sure, the Close_Date format is:

2015-05-20 09:15:30

[stephanefotso]

Can i have the format of Close_Date? A sample data.
Thanks