I am indexing data from a ticketing tool. I need to see what tickets were opened at end of each month. I've done a initial charge of the database, because of this, I can't use the time indexed, otherwise I have to use opendate and close_date. Basically, the logic that I need to apply is:
Make a count of all tickets that were opened before end of month and were closed after the end of that month. I need show like timechart with this info by month.
Any idea about the way to get this info? Maybe could be useful the gentimes command?
Since you need last day of Current month for your evaluation purpose you can make use of the following eval expression to come up with the same
| eval current_month_last_day=relative_time(now(),"+1mon@mon-1d").
Following is the run anywhere query which you can use to test dates like 05/30 and 05/31 for open_date for the current month:
| makeresults | eval open_date=strptime("2017/05/31 13:55:00","%Y/%m/%d") | eval close_date=strptime("2017/06/03 10:23:00","%Y/%m/%d") | eval current_month_last_day=relative_time(now(),"+1mon@mon-1d") | where close_date>current_month_last_day AND open_date=current_month_last_day | fieldformat open_date=strftime(open_date,"%Y/%m/%d") | fieldformat close_date=strftime(close_date,"%Y/%m/%d") | fieldformat current_month_last_day=strftime(current_month_last_day,"%Y/%m/%d")
What is your criteria for End Of the month?
Following is a run anywhere search which takes opendate>25 as End of the Month and find records where closemonth changes.
PS: makeresults and First two evals for opendate and closedate are to mock the data.
| makeresults | eval open_date=strptime("2017/01/26 13:55:00","%Y/%m/%d %H:%M:%S") | eval close_date=strptime("2017/02/03 10:23:00","%Y/%m/%d %H:%M:%S") | eval open_month=strftime(open_date,"%m") | eval open_day=strftime(open_date,"%d") | eval close_month=strftime(close_date,"%m") | where close_month>open_month AND open_day>"25"
Thanks for the response. My end of month criteria is last day of each month... It's dependent of the month. My problem is that I have to compare a date that does not exist in any field (31th january, 28th february, 30th march....) with the fields opendate and closedate, and then put in a chart how many tickets were open in each end of month.
I've tried what you purposed to me and it does not work for my requisite, however, thanks!