Can Historical Data be modified

[bagendron]

I wouldn't dream of doing this under normal circumstances but we just discovered that I was given the wrong multiplier for a calculated item. We've been collecting for at least six months and I was wondering if there was a way to go back through the historical data and correct the error? I've thought of using a SQL query but I don't know the schema well enough to risk it without guidance.

Thanks in advance for the help.

[Linwood]

Well, the short answer is yes, but the longer answer presumes you are comfortable enough with SQL to do it.

Before I write the rest, it is worth saying - are you sure you don't want to just unlinke/clear the template and put it back on, and start collecting fresh and correct? I know sometimes historical data is valuable, but if this is new stuff you set up, ask whether it really matters if it started last week or today?

Data is going to be (most like) in both a trend and a history table. One are individual item values (history), one are periodic rollups of averages (trend). Each has multiple tables, e.g. history, history_uint, history_str, history_text. Trend data is similar but does not include text data (not an issue for you as you said it was calculated). Your data will only bein one, by type, e.g. unit is unsigned int and reflects unsigned numeric items.

For both history and trend there is an itemid field. This links to the host's specific item that generated the data. You can find it by a query more or less like this (doing this off the cuff not testing);

select i.itemid, i.name, i.key_, h.host
from hosts h
inner join items i on i.hostid=h.hostid
where h.host='whatever'

Look for your host with that and get the itemid you need from the description and key.

This will identify all the data in the history and trend tables for that item, the catch is that you likely have some that is valid and some not. The actual data is tagged with a "clock" which is a unix time stamp, so you have to figure out the point at which you want to change. You can also probably see this if you select the history data in order of clock (and "ns" secondarily which is a more precise portion of the time). If the multiplier was grossly wrong you can likely see the point in time at which it changes.

You can simply update, e.g.

update history_uint
set value = value / 8
where itemid=xxxx and clock < 123123123

once you know the clock cutoff. Then do trend. Note that there's likely a trend period where it is both wrong and right in the same period since it is a rollup.

If it's a ton of devices and you are good in sql all this can be done in one sql statement of course, with a list, though the format depends on your sql flavor (mysql, postgresql, etc.)

Changing the value is safe per se, so long as you properly isolate the item id.

If your installation uses partitioned tables be sure to involve a knowledgeable DBA to know if it's safe to change the history based on how partitions are rolled off, and whether they are writeable.

But think about just "unlink/clear, and start over".

[bagendron]

Thanks!

This is exactly what I need. I'm comfortable with SQL, I wasn't comfortable with the schema as it applies to history and trends. I wasn't sure how many tables were involved. I'll forward your advise to the affected user and try to talk him out of the db update.

Thank you for the help!

[Linwood]

Good. Yes, historical information will be in only those two tables (or if old enough only trends).

Note that trend and history DO overlap for recent periods, it is not a case that history ages off and becomes trends.