Need help to calculate time delta in ES/Kibana

I need some help to calculate time difference in one of our indexes. These are application logs, where the application creates and deletes new virtual machines. The log format is as follows:

   @timestamp			        clusterid	    action
May 15th 2017, 17:20:11.080	 cluster1	/action/add
May 15th 2017, 20:21:11.080	 cluster2	/action/add
May 15th 2017, 21:25:11.080	 cluster1	/action/delete
May 15th 2017, 22:35:11.080	 cluster2	/action/delete
May 16th 2017, 10:20:11.080	 cluster2	/action/add
May 16th 2017, 15:51:11.080	 cluster3	/action/add
May 17th 2017, 20:30:11.080	 cluster3	/action/delete

Here we need to calculate the time difference(in hourly format) for each clusterid between /action/add and /action/delete. If there is no /action/delete for a specific cluster id, it should be able to show time delta(in hourly format) till now.

I've been researching on this for past couple of days, and written the following query for 'painless' scripted field.

"script": {
    "lang": "painless",
    "inline": "
int totallogs= ctx._source.action.size();    
int datetimediff=0;      
int clusterstarttime=0;      
String clusterid;      
int clusterendtime=0;       
for(int i = 0; i < totallogs; ++i)
{        if(ctx._source.url[i]=='/action/add')
{          clusterstarttime=ctx._source.timestamp[i];          clusterid=ctx._source.clusterid[i];        }        if(ctx._source.action[i]=='/action/delete' && ctx._source.clusterid == clusterid){          clusterendtime=ctx._source.timestamp[i];          
datetimediff=clusterstarttime - clusterendtime;        }      
return datetimediff;      }    "

However, this isn't working at all!
I've referred to a number of discussions on elastic blog, but nothing has been helpful so far.
here are a few of the posts I've followed:

Most of these posts talk about using a 'scripted field'; but none of them show even a single and simplest example. And as we all know, Kibana and Elasticsearch online documentation is utterly pathetic!

So, can anyone help me with this?


I've seen in another post that you can extract parts of the date as Integer in a scripted field:
Integer.parseInt(new SimpleDateFormat("HH").format(new Date(doc["created_on"].value)))

With that approach it should be possible to do the subtraction

Thanks @th_dap7, could you share that link? if you are able to find it?


1 Like

@th_dap7 thanks for sharing the link! I'll try it out!

Hi @uttkarshd
Did it work? I'm dealing with the same issue
please share :slight_smile:

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.