What time is it? -- A cheap solution to make Kibana show histogram in logged timezone

Kibana3 supports showing histograms in browser timezone or in UTC timezone. Suppose I have this set of data:

timestamp	event	@timestamp	indexName
2014-09-27- 06:44:47.954 EDT	cluster failed	2014-09-27T 10:44:47.954Z	logstash-test-testedt-2014.09.27
2014-09-27- 06:45:18.048 EDT	cluster failed	2014-09-27T 10:45:18.048Z	logstash-test-testedt-2014.09.27
2014-09-27- 06:45:48.145 EDT	cluster failed	2014-09-27T 10:45:48.145Z	logstash-test-testedt-2014.09.27
2014-09-27- 21:10:10.499 EDT	non-fatal DB  exceptions	2014-09-28T 01:10:10.499Z	logstash-test-testedt-2014.09.28

The following two charts show the above data, left is in the browser timezone (which is GMT+8), the right in UTC timezone:

I am very bad at time conversion, if the customer calls “I run into an issue in 2014-09-27-06:44:47.954 EDT”, you mind goes slowly “EDT is 4 hours behind UTC, without daily light saving, it is 5 hours behind UTC, 9/27 is probably not in daily light saving, so  2014-09-27-06:44:47.954 EDT is 2014-09-27T10:44:47.954Z…”

When I analyze one single customer’s logs, I generally do not care what time it exactly is. I want to be able to quickly locate the time “2014-09-27-06:44:47.954” in the chart.  For that, I need the chart to show events in their logged timezone:

It is not easy to convert a timestamp string with a timezone abbreviation to a date because of day light saving and because the same timezone abbreviation can mean different timezones, for example, CST can mean “Central Standard Time” or “China Standard Time”. Converting a timestamp string to a date should have been done by Logstash (please refer to LogStash Cookbook).

Since what I care about is “what I see is what I get”, I thought of a cheap solution. I pretend 2014-09-27-06:44:47.954 EDT is actually 2014-09-27-06:44:47.954 GMT, and show it in Kibana in UTC timezone. 

To do this, first I need to create a field “nativetimestamp” which stores the 2014-09-27-06:44:47.954 GMT:

mutate{

             add_field=>{"nativetimestamp"=>"%{timestamp}"}                               

       }

       mutate{

             gsub => ["nativetimestamp","[A-Za-z]+","GMT"]      

       }

The above creates field “nativetimestamp” whose value is copied from field timestamp,  and then changes its timezone abbreviation to GMT, so now 2014-09-27-06:44:47.954 EDT becomes 2014-09-27-06:44:47.954 GMT. 

I also moved the timeoptions from panels to dashboard, because I want all panels to have the same time x-axis for comparison:

 

 This solution is cheap enough, and yet to add some complexity into the mix:

• The date in an index name is UTC.

• There is another timezone in the play, browser timezone. Kibana’s timepicker control seems to translate user’s input into UTC, to correct it, Kibana implements some hacks. I want to show people’s input the same as they input (without converting it to UTC), while sending the input as UTC for downstream processes.

For example, in the above diagram, from~to time range is set to 2014-09-27 10:00:00.000 ~ 2014-09-28 02:00:00.000, which precisely captures the sample dataset, as the x-axis shows:

To achieve this, I also implemented some hacks, such as:

//pretend a local date to be a utc date
//e.g. local date is 2014-11-20-00:00:00+0800, the utc date should be 2014-11-19-16:00:00+0000
//pretend the local date to be utc 2014-11-20-00:00:00+0000
var pretendLocalToUtc = function (date) {
    var dateStr = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate()
        + "-" + pad(date.getHours(), 2) + ":" + pad(date.getMinutes(), 2) + ":" + pad(date.getSeconds(), 2) + ":" + pad(date.getMilliseconds(), 2);
    return moment(dateStr + " +0000", "YYYY-MM-DD-HH:mm:ss.SSS Z").toDate();
};

//pretend a utc date to be local

//e.g. utc date is 2014-11-20-00:00:00:00 +0000, the local date should be 2014-11-20-08:00:00:00 +0800

//pretend the utc date to be 2014-11-20-00:00:00:00 +0800

var pretendUtcToLocal = function (date) {

    var dateStr = date.getUTCFullYear() + '-' + (date.getUTCMonth() + 1) + '-'

    + date.getUTCDate()+ "-" + pad(date.getUTCHours(), 2) 

+ ":" + pad(date.getUTCMinutes(), 2) + ":" + pad(date.getUTCMilliseconds(), 2)

 + ":" + pad(date.getMilliseconds(), 2)

    var timezoneOffset = moment(date).zone() / 60;

    var behindUtc=timezoneOffset>0;

    timezoneOffset=Math.abs(timezoneOffset);

    if (timezoneOffset < 10 ) {

        timezoneOffset = '0' + timezoneOffset;

    } else {
        timezoneOffset = timezoneOffset + '';
    }

    timezoneOffset = reversePad(timezoneOffset, 4);

    return moment(dateStr + (behindUtc?"-":"+") + timezoneOffset,  

        "YYYY-MM-DD-HH:mm:ss.SSS Z").toDate();

};

There is a caveat, the date in the index name is of UTC timezone, when you set from~to range, you are also selecting indexes within that range. If you use nativetimestamp field, you have to be careful to set the range to include the indexes. 

timestamp	nativetimestamp	@timestamp	indexName
2014-09-27- 06:44:47.954 EDT	2014-09-27- 06:44:47.954 GMT	2014-09-27T 10:44:47.954Z	logstash-test-testedt-2014.09.27
2014-09-27- 06:45:18.048 EDT	2014-09-27- 06:45:18.048 GMT	2014-09-27T 10:45:18.048Z	logstash-test-testedt-2014.09.27
2014-09-27- 06:45:48.145 EDT	2014-09-27- 06:45:48.145 GMT	2014-09-27T 10:45:48.145Z	logstash-test-testedt-2014.09.27
2014-09-27- 21:10:10.499 EDT	2014-09-27- 21:10:10.499 GMT	2014-09-28T 01:10:10.499Z	logstash-test-testedt-2014.09.28

 

For example, when you choose nativetimestamp field, because you want to get what you see, you might want to set the range to be 2014-09-27 06:00:00.000 ~ 2014-09-27 22:00:00.000, but then you will miss index logstash-test-testedt-2014.09.28, because it is out of the range. You will need to input 2014-09-27 06:00:00.000 ~ 2014-09-28 00:00:00.000 to hit both indices. 

It is not perfect, but hey, I told you it is a cheap solution.(There is a cheap solution for this too, I just need to add 2 days into the from~to range, and all indices will be sure to be picked. So now this is a cheap solution through and through.)