Hello...
I have an index built from the log of a requests/incident ticket reporting/tracking system. Each incident has the following time fields present:
- open_time (the time the incident/req was submitted); and
- close_time (the time the ticket was declared closed on the system).
I did not build the index and there are some date/time storage issues that I've asked about in a separate post. For now, let's assume they all exist in ES in a format that will allow me to do date arithmetic.
Here is what I want to build:
I want to run a sliding window of fixed width--let's call it $\Delta t$--and this window would define a time interval $[t_i , t_i + \Delta t)$ for my analysis. Within this window, I'd like to find all tickets that are "currently open"--that is, they have open_time < $t_i + \Delta t$ and close_time >= $t_i + \Delta t$. I'd also like to determine the distribution of ages with respect to the center of the window--that is, compute age = $t_i + \Delta t/2$ - open_time. I would then like to do some visualisation and analysis of these data over this timeline I have created--e.g., "average age of open tickets" for each window location. So, I would get a new timeseries...probably stashing it in a new index.
Looking around Kibana's documentation, the answer isn't jumping out at me. Kibana's Timelion appears also not to be capable of dealing with my problem.
I'm currently thinking of handling this as follows...Write python code that uses the ES package and run on another server to generate and submit JSON-format searches to the ES server. Thus the looping logic is handled in python and the date/time--and all other information--from the hits comes back as JSON (which is easy to handle from python). I could then process the JSON I get back to compute the tickets'age distribution at that point in time. I'd accumulate this data on my python server and then generate a new index to put back on the ES server for subsequent visualisation using Kibana. The downside to this approach is of course that each position of the sliding window corresponds to an ES search that could in principle return large numbers of hits and there no doubt would be a lot of redundancy in this technique.
Is there an easier/more elegant way to do this? If so, how?
Thanks in advance for any pointers you can provide...