topic Re: Timechart based on date and time string NOT the timestamp in Splunk Search

Timechart based on date and time string NOT the timestamp

JWBailey — Mon, 28 Sep 2020 20:01:17 GMT

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,

Re: Timechart based on date and time string NOT the timestamp

stephanefotso — Wed, 20 May 2015 22:10:43 GMT

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

Re: Timechart based on date and time string NOT the timestamp

JWBailey — Wed, 20 May 2015 22:22:16 GMT

Sure, the Close_Date format is:

2015-05-20 09:15:30

Re: Timechart based on date and time string NOT the timestamp

stephanefotso — Mon, 28 Sep 2020 20:02:36 GMT

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

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
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
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

Retrive the last value of Close_Date, and take it as the initial date. Let's call it initial_date
Extract the year, month, day, hour, minutes, and seconds from initial_date
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
evaluate the target_Close_Date

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.

Re: Timechart based on date and time string NOT the timestamp

JWBailey — Mon, 28 Sep 2020 20:09:49 GMT

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

I use strptime to convert Close_Date to epoc time. [ ...| eval CD=strptime(Close_Date, "%Y-%m-%d %H:%M:%S") | ... ]
I then use relative_time to calculate the epoc time of my benchmarks, 30 days, 60 days, and 90 days ago. [ ... | eval days30=relative_time(now(),"-30d@d") | eval days60=relative_time(now(),"-60d@d") | eval days90=relative_time(now(),"-90d@d") | ... ]
Use a nested if statement to calculate which window each event falls into. [ ... | eval Age=if(CDdays90,60,if(CDdays60,30,0))) | ... ]
Get rid of the events that were closed in the last 30 days (different than my initial question... I know..). [ ... | where Age>1 | ... ]
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