2018-11-15

Excel datetime from Nagios CSV export

When you export a counter set like Processor % Priviledged Time from Nagios XI to a CSV-file, you get a file that begins with something like this:

'timestamp','\Processor($)\% Privileged Time_value'
'1540989000','6.0683333333e+00'
'1540990800','1.2750000000e+00'
...


The timestamp column is just a integer but I would like a human readable timestamp. At first I looked at a conversion from a file time, but the integer from Nagios is too small to give a correct datetime.

A good colleague then introduced me to Epoc timestamp, and with this formular in Excel the Nagios timestamp is converted correct:
=(A2/86400)+25569
where
  • "A2" is the reference to the cell holding the Nagios timestamp integer
  • 86400 is the Unix timestamp for the first day of the year 1970 in the Gregorian calendar, having 365 days.
  • 25569 is the timestamp for the same day (1970-01-01) in Excel calculated with =DATE(1970;1;1).
When you enter the formular in Excel the first result is not that human readable, e.g. 43404.52083. But by formatting the cell as a timestamp or a user defined type like
yyyy-mm-dd hh:mm:ss,000
you get a nice and precise timestamp:
2018-10-31 12:30:00,000

The number in the second column can also be formatted to something more readable by formatting the cells to a number, e.g. with one decimal.

Please notice that I am working in a danish Excel where the decimal delimiter is comma (,). I have tried to convert to the more international decimal delimiter dot (.), but the conversion is done manually in this text and not tested. So you could experience some errors on this minor subject, but I am quite confident that you can fix them quite fast.

You can play with Epoc conversions back and forth on EpocConverter.

No comments: