Need help to calculate time delta in ES/Kibana

[uttkarshd (Uttkarsh Dharmadhikari)]

Hi,
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:

Plot latency measure based on two time fields in an index

Hi All: I am not sure if this has been asked and answered (my searches have not returned results for this), So here goes : I have an index with a timestamp of when a document was persisted to the index as well as a timestamp of when the document was actually created. I would like to measure the latency as the difference between the two time stamps and then plot it in Kibana, each message has a unique identifier. Is this possible with Timelion or with the Vanilla Kibana. Any help is much …

Kibana 5.1 possible to calculate time difference and place it in a field in kibana at runtime?

Hi there, I know I can do many cool stuff in logstash, for example calculating time differences and store it in a field. Now my question, is it possible to do such stuff in kibana in realtime too? Say, I have following timestamps in my event @timestamp timestampStart timestampEnd Is it possible to calculate a "virtual" field "timeDiff" in kibana by calculating timestampEnd-timestampStart? I don't need to search by these fields, but aggregating on them would help. Other familiar topic is,…

Calculate time using timestamp!

Hi, guys!!! I want to calculate how much time a user has spent in the game. I have my documents with fields: timestamp, message. Message field can be 2 different types: "log in" and "log out". Difference in time between "log in" and "log out" is the time my user has spent in my game. Is it possible to calculate the time the user has spent in the game for a month?

How do you use script fields with datetime subtraction

I am trying to use scripted fields to subtrack two datetime fields to determine how long it took for a file to processed. doc.['time_var1'].value - doc.['time_var2'].value doc being the index name. I get this error: Visualize: Request to Elasticsearch failed: {"error":"SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures {[rtl_z8lBRrGHGQPtgehi2w] my time_var1 is something like this: 2016-01-13 16:34:59 the same format for the time_var2. Is there s…

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?

Thanks,
Utkarsh

[th_dap7 (Torsten)]

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

[uttkarshd (Uttkarsh Dharmadhikari)]

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

[th_dap7 (Torsten)]

@th_dap7

SimpleDateFormat("HH").format(new Date(doc["created_on"].value

Sure: https://discuss.elastic.co/t/extracting-hour-from-date-field-scripted-field/691/6

[uttkarshd (Uttkarsh Dharmadhikari)]

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

[avivc]

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