Create a evaluation in each end of month based in one field that is not _time field

[nsanchezfernand]

Hi.

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

Thanks.

[niketn]

[Updated Answer]
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 open_date>25 as End of the Month and find records where close_month changes.
PS: makeresults and First two evals for open_date and close_date 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"

[nsanchezfernand]

Hi, niketnilay.

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 open_date and close_date, 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!

Thanks.

[aorkcreate]

I've needed the same output ,did you achieve that ,can you tell me how is it possible ?