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 Close_Date. I need the count of events broken down into various windows: Close_Date < 30 days ago, Close_Date between 30 and 60 days ago, Close_Date 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,
Thank you for the response. I also found another way to do this. Here is the solution I used:
[ ...| 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(CDdays90,60,if(CDdays60,30,0))) | ... ]
[ ... | where Age>1 | ... ]
[ ... | 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
Thank you for the response. I also found another way to do this. Here is the solution I used:
[ ...| 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(CDdays90,60,if(CDdays60,30,0))) | ... ]
[ ... | where Age>1 | ... ]
[ ... | 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
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
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
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.
Sure, the Close_Date format is:
2015-05-20 09:15:30
Can i have the format of Close_Date? A sample data.
Thanks