I imagine not many people are contending with this issue so long after the launch of 1.0.0, but for what it's worth, coming from a very old version of cacti + nectar, here's what I had. The old tables had been "reports" and "reports_data", and when I converted those to current report records, in freshly created "reports" and "reports_items" tables, I did that as follows:
I'm still not current on cacti, and there may be other changes by the time anyone uses this, so move carefully.
1) rename table reports to reports_bk;
rename table reports_data to reports_data_old;
2) Create tables using the sql found in cacti.sql for reports and reports_items;
3) Test the following two queries to convert the backed up data into the current tables, after testing;
For testing, I had created one new report with a variety of items, and I ran the following queries with the "insert" part first commented out, so I could compare the freshly created report to the modified selects on the old report data.
I did several iterations of this til everything seemed to look pretty good.
4) For the date field, I was lucky that I didn't need a large variety of different "first" dates. I knew I wanted them all to run the next day, so I just set the one time for all of them. You might just do that anyway, but once they're inserted, just fix them in the UI, since fixing just the one thing is probably easier than doing all the date math.
5) delete all test reports you've created, from reports and reports items, so the ids don't conflict (or write an offset into the queries below, if you already have other reports you want to keep).
6) so the actual report recreation
Code: Select all
-- insert into reports
SELECT
id as id,
1 as user_id,
name as name,
'' as cformat,
'default.format' as format_file,
16 as font_size,
1 as alignment,
'' as graph_linked,
2 as intrvl,
1 as count,
0 as offset,
unix_timestamp('2018-04-04 10:00:00') as mailtime,
concat('Cacti Report - ',name) as subject,
'Zayo Customer Portal' as from_name,
'cacti@customerportal.zayo.com' as from_email,
trim(replace(replace(replace(email,'\n',' '),'\r',' '),'\t',' ')) as email,
'' as bcc,
1 as attachment_type,
200 as graph_height,
600 as graph_width,
1 as graph_columns,
'' as thumbnails,
lastsent as lastsent,
'' as enabled
FROM cacti.reports_bk
Code: Select all
-- INSERT INTO `cacti`.`reports_items`
-- (`id`, `report_id`, `item_type`, `tree_id`,
-- `branch_id`, `tree_cascade`, `graph_name_regexp`, `host_template_id`,
-- `host_id`,`graph_template_id`, `local_graph_id`, `timespan`,
-- `align`, `item_text`, `font_size`, `sequence`)
SELECT
rdo.id,
reportid,
case item when 'graph' then 1 when 'text' then 2 when 'tree' then 3 else 0 end as item_type,
0 as tree_id,
0 as branch_id,
'' as tree_cascade,
'' as graph_name_regexp,
case when item != 'graph' then 0 else h.host_template_id end as host_template_id, -- host tmpl id
rdo.hostid,
case when item != 'graph' then 0 else gl.graph_template_id end as graph_template_id,
case when item != 'graph' then 0 else rdo.local_graph_id end as local_graph_id, -- loc gr id
8,
1,
case when data != '' and data is not null then data else '' end as item_text,
16,
rdo.gorder -- sequence
FROM reports_data_old rdo
left join graph_local gl on rdo.local_graph_id = gl.id
left join `host` h on h.id = rdo.hostid
