Changing deadband/interpolation on all tags in system

I was told that there was a way to do this with a query when I was on the phone with support. What is the query? They also said I would have to restart the gateway after making the change.

I kow I can do it in scripting but I have so many different tags that It would take alot of time to write the script.

I am going to try with the csv file but am kind of worried that reimporting 126k tags might hose up the gateway, so I was just trying to have a backup plan.

Yes, there is a query you can run in the internal database. It would be better to try with the CSV file. I don’t think it will hose up the system. Of course make a backup of your tags prior to doing anything.

You can also multi-edit the tags in designer. eg: select a whole bunch then click edit and apply the same edit to all selected tags.

Could still be tedious for 126K tags…

yeah that wasnt an option because its only about 35 tags per folder.

it went moderately well. had a couple of weird things in the csv file, like multiple folders, but Id delete them and reload. then toward the end it threw an error and all the folders existed but none of the tags were in it. Then I deleted everything listed under the tag provider and reloaded the csv and it took everything as it should have.

But yeah it took quite a while to load 126K tags.

Is this a query that is already written and tested? If so, could you please provide it? This is something we’ve had on our “to-do” list for a while now since we have a number of temperature alarms that will constantly bounce on/off. I’ve been meaning to research it on my own, but if there is an existing one I would feel a lot more comfortable using that.

I don’t know the query off hand, but this really is something that’s better to do in the CSV file.

The problem with using the CSV is that there are only about 600 points that we would like to do this for. The alerts that we would like to modify are all prefixed by “High Temp” or “Low Temp”, so with the query method it would be as simple as adding a where clause to the end to limit the tags we want to modify. We would also like to modify the time deadband as well for these, which seems to be wrapped up within a set of colon separated values within the “Alarm States” column:

Low Temp;Low;0.0;20.0;2;;;0.0;SEC$High Temp;Medium_Low;75.0;1.0;8;;;0.0;SEC$

Well, if you insist on messing with the internal db directly, backup the gateway, then go to the gateway configuration page and look under system -> console. Pick the advanced tab. Read the warning, then proceed.

On the right is a complete list of the internal tables. At the top is a SQL box.

Now your on your own :smiley:

The original post was talking about history interpolation mode. What are you trying to change, the alert deadband? They would be very different queries. And unfortunately, it wouldn’t be easy, because you would be updating the double value column of a property named “AlertDeadband” in the table “SQLTAGPROP”… if it existed, and adding it if it didn’t. But, that table references the “SQLTAG” table by tag id, so instead of a simple where clause, you would be doing some sort of “insert or update” (which I’m not sure the internal db supports) along with a join.

Side note: if you do anything directly to the internal db, you’ll have to edit and save the tag provider to get it to reload the tags. Second note: make sure to make a backup before doing anything.

Ultimately, I would play around with CSV, and then perhaps use a good text editor that supports macros like TextEdit or SublimeText to [find “Low Temp”, find 0.0, change to 1.0, continue].

Regards,

Hi Colby,

Yes, sorry, I had misunderstood that this thread was about history deadband rather than alert deadband.

I’m under the impression from this and other posts that the database tables which FactorySQL/FactoryPMI used to use (sqlt_core, sqlt_meta, sqlt_as, etc) are no longer “publicly” accessible in newer versions of Ignition. Is this the case? We had upgraded from FactorySQL/PMI about a year and a half ago, but still have all of our SQLTag tables accessible through the MySQL database that stores all of our transaction group history.

Does this type of setup combined with our currently not having any deadband entries make things any easier via SQL? The only reason I press this issue is because I feel it is much easier to introduce issues via CSV vs SQL. Of course we’ll have backups, but minimal downtime is preferable if at all possible.

Otherwise, if I export, edit, then re-import SQLTags via CSV, will this setup affect the process in any way? I definitely like the idea of using macros in a text editor to accomplish this if necessary. Pretty slick. :thumb_left:

[quote=“klesher”]Hi Colby,

Yes, sorry, I had misunderstood that this thread was about history deadband rather than alert deadband.

I’m under the impression from this and other posts that the database tables which FactorySQL/FactoryPMI used to use (sqlt_core, sqlt_meta, sqlt_as, etc) are no longer “publicly” accessible in newer versions of Ignition. Is this the case? We had upgraded from FactorySQL/PMI about a year and a half ago, but still have all of our SQLTag tables accessible through the MySQL database that stores all of our transaction group history.

Does this type of setup combined with our currently not having any deadband entries make things any easier via SQL? The only reason I press this issue is because I feel it is much easier to introduce issues via CSV vs SQL. Of course we’ll have backups, but minimal downtime is preferable if at all possible.

Otherwise, if I export, edit, then re-import SQLTags via CSV, will this setup affect the process in any way? I definitely like the idea of using macros in a text editor to accomplish this if necessary. Pretty slick. :thumb_left:[/quote]

depending upon how your tags are laid out you could perhaps use the ialabs scripting module.

They’re still available, as an “External Provider”, which it seems you’re using. With Ignition, we’ve transitioned more things to internal tags, as the performance is generally better, but external tags are still used often when you want to do things like share tags between systems, and edit them externally.

Does it make it easier? Probably a bit. First off, you don’t need to do it from the tiny web console, but can instead use the mysql query browser or some other tool. Second, mysql supports some cool query constructs that the internal database doesn’t, most notably Insert, On Duplicate Update

Ultimately, you’re trying to get (tagid, name=‘AlertDeadband’, floatval=1.0) in to that table (or whatever deadband you want), for each tag you’re interested in.

You could probably do lots of things. Here’s what I’d suggest:

  1. Hone down the right select query for the tags you want to update:
SELECT * FROM sqlt_as WHERE statename LIKE '%High Temp%' OR statename LIKE '%Low Temp%'
  1. Once happy, use that to build up an INSERT…SELECT…ON DUPLICATE query, with the select building the data to insert:
INSERT INTO sqlt_meta(tagid, name, floatval) SELECT tagid, 'AlertDeadband', 1.0 FROM sqlt_as WHERE statename LIKE '%High Temp%' OR statename LIKE '%Low Temp%' ON DUPLICATE KEY UPDATE floatval=1.0
  1. Once done, reload the tags in Ignition by setting the “configchange” column of “sqlt_core” to “now”:
UPDATE sqlt_core SET configchange=now()

Hope that helps, let me know if you have any questions!

Regards,