icingadb/cmd/icingadb-migrate/embed/downtime_query.sql

SELECT dh.downtimehistory_id, UNIX_TIMESTAMP(dh.entry_time) entry_time, dh.author_name, dh.comment_data,
  dh.is_fixed, dh.duration, UNIX_TIMESTAMP(dh.scheduled_start_time) scheduled_start_time,
  COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) scheduled_end_time, dh.was_started,
  COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) actual_start_time, dh.actual_start_time_usec,
  COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) actual_end_time, dh.actual_end_time_usec, dh.was_cancelled,
  COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) trigger_time,
  COALESCE(dh.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', dh.downtimehistory_id, '-', dh.object_id)) name,
  o.objecttype_id, o.name1, COALESCE(o.name2, '') name2, COALESCE(sd.name, '') triggered_by
FROM icinga_downtimehistory dh USE INDEX (PRIMARY)
INNER JOIN icinga_objects o ON o.object_id=dh.object_id
LEFT JOIN icinga_scheduleddowntime sd ON sd.scheduleddowntime_id=dh.triggered_by_id
WHERE dh.downtimehistory_id BETWEEN :fromid AND :toid
AND dh.downtimehistory_id > :checkpoint -- where we were interrupted
ORDER BY dh.downtimehistory_id -- this way we know what has already been migrated from just the last row's ID
LIMIT :bulk
cmd/ido2icingadb: go:embed large queries for the sake of syntax highlighting. 2022-05-19 05:46:49 -04:00			`SELECT dh.downtimehistory_id, UNIX_TIMESTAMP(dh.entry_time) entry_time, dh.author_name, dh.comment_data,`
			`dh.is_fixed, dh.duration, UNIX_TIMESTAMP(dh.scheduled_start_time) scheduled_start_time,`
cmd/icingadb-migrate: ignore icinga_downtimehistory#was_started=0 rows Such appear e.g. for scheduled but never triggered flexible Downtimes. Not triggered = no events. 2023-07-27 11:29:33 -04:00			`COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) scheduled_end_time, dh.was_started,`
cmd/ido2icingadb: go:embed large queries for the sake of syntax highlighting. 2022-05-19 05:46:49 -04:00			`COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) actual_start_time, dh.actual_start_time_usec,`
			`COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) actual_end_time, dh.actual_end_time_usec, dh.was_cancelled,`
icingadb-migrate: Mitigate NULL names in {comment,downtime}_query Historical data from an older Icinga 2 installation contained NULL values for the name column in some rows of the icinga_commenthistory and icinga_downtimehistory tables. Normally this field contains something like ${name1}!${name2}!${unique_value} where the $unique_value is based on a timestamp for older entries and a UUID for newer ones. For a concrete example, this could be "host.example.com!ping6!123…". Unfortunately, using an empty string for these NULL values will cause an error later because the new primary key will be calculated based on it. Therefore, a new deterministic name is generated based on the primary keys and the known name1 and name2 values. Closes #766. 2024-07-09 07:05:36 -04:00			`COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) trigger_time,`
			`COALESCE(dh.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', dh.downtimehistory_id, '-', dh.object_id)) name,`
			`o.objecttype_id, o.name1, COALESCE(o.name2, '') name2, COALESCE(sd.name, '') triggered_by`
cmd/ido2icingadb: go:embed large queries for the sake of syntax highlighting. 2022-05-19 05:46:49 -04:00			`FROM icinga_downtimehistory dh USE INDEX (PRIMARY)`
			`INNER JOIN icinga_objects o ON o.object_id=dh.object_id`
			`LEFT JOIN icinga_scheduleddowntime sd ON sd.scheduleddowntime_id=dh.triggered_by_id`
cmd/ido2icingadb: allow custom input data time range 2022-09-06 09:50:04 -04:00			`WHERE dh.downtimehistory_id BETWEEN :fromid AND :toid`
			`AND dh.downtimehistory_id > :checkpoint -- where we were interrupted`
cmd/ido2icingadb: improve comments 2022-05-19 11:11:45 -04:00			`ORDER BY dh.downtimehistory_id -- this way we know what has already been migrated from just the last row's ID`
cmd/ido2icingadb: go:embed large queries for the sake of syntax highlighting. 2022-05-19 05:46:49 -04:00			`LIMIT :bulk`