Displaying @datetime in UTC format from Elasticsearch database rollup using Kibana

[EricJohnson]

Greetings!

We are using Elasticsearch rollups of metricbeat data using Kibana. Instead of @datetime we would like to see the date in UTC. I cannot find an option to do this in Kibana. We just need to convert the field "@timestamp.date_histogram.timestamp" in UTC format. I.e., we want '07-23-2019T19:34:00.000000' rather than '1563910440000'.

Any suggestions will be greatly appreciated.

I tried to modify the JSON configuration directly but Kibana will not allow it.

This is the JSON configuration file for the rollup:
{
"config": {
"id": "cpu_daily_rollup",
"index_pattern": "metricbeat-*",
"rollup_index": "cpu_daily",
"cron": "0 0 0 * * ?",
"groups": {
"date_histogram": {
"fixed_interval": "24h",
"field": "@timestamp",
"delay": "30m",
"time_zone": "UTC"
},
"histogram": {
"interval": 5,
"fields": [
"system.cpu.total.pct"
]
},
"terms": {
"fields": [
"system.cpu.total.pct",
"fields.oss.id",
"system.cpu.cores"
]
}
},
"metrics": [
{
"field": "system.cpu.total.pct",
"metrics": [
"avg",
"max",
"value_count",
"sum",
"min"
]
},
{
"field": "@timestamp",
"metrics": [
"value_count"
]
}
],
"timeout": "20s",
"page_size": 1000
},
"status": {
"job_state": "started",
"current_position": {
"@timestamp.date_histogram": 1564963200000,
"fields.oss.id.terms": "5a4fe62140a7078a7930ceac",
"system.cpu.cores.terms": 2,
"system.cpu.total.pct.histogram": 0,
"system.cpu.total.pct.terms": 1.948
},
"upgraded_doc_id": true
},
"stats": {
"pages_processed": 17,
"documents_processed": 5424850,
"rollups_indexed": 15490,
"trigger_count": 1,
"index_time_in_ms": 4725,
"index_total": 16,
"index_failures": 0,
"search_time_in_ms": 47329,
"search_total": 17,
"search_failures": 0
}
}

Thank you!

[flash1293 (Joe Reuter)]

Hi @EricJohnson

your rollup config is looking fine. It is normal that the date fields are saved as timestamps in Elasticsearch, but Kibana should definitely format them correctly. Where exactly in Kibana do you see the raw timestamps? Does the field have the right type in the index pattern?

[EricJohnson]

Thank you for the reply!

We see raw timestamps if we select the timestamp field in the rollup while looking at the "Discover" tab in Kibana. These "@timestamp.date_histogram.timestamp" fields are in the "1564963200000" format that we want to see as UTC. There is not, as far as I can see, any option to view them in the format we need.

[flash1293 (Joe Reuter)]

If you go to Management > Index Patterns > <YOUR ROLLED UP INDEX PATTERN>

what type does the @timestamp.date_histogram.timestamp field have? If it doesn't have a date type that could be the problem.

[EricJohnson]

It has "date" as expected.

[EricJohnson]

Nothing changes if I go to "Edit" and change the Date format to "MMM D, YYYY @ HH:mm:ss:SSS". It still just shows up as a long number.

[flash1293 (Joe Reuter)]

Ah, sorry, I forgot - You cannot view rollup indices in Discover because discover is a tool to view individual documents. In rollup indices the original documents are not present anymore. If you want to see the count of documents over time, you can configure this kind of chart in Visualize based on the rollup index and everything will get formatted correctly.

If you absolutely want to look at the the raw rolled up documents, you can create a standard index pattern that matches only the rolled up indices, then the rolled up document groups are shown as individual documents, which is probably more confusing than helpful. It's easier to stick with Visualizations and Dashboard to explore rolled up data.

[EricJohnson]

Thank you again for looking into this for me. I think that answers the question! Thank you!