Hey guys,
It would be nice if, for tag value changes, there was another column in the AUDIT_EVENTS table that would store the tag value that it was changed from. We’ve had a couple of incidents where someone changed a value or changed a wrong value by accident and it was easy to find out who did it but not what the original value was.
I am with you that would be a nice feature.
I hope this help somebody.
SELECT
ae.audit_events_id,
ae.event_timestamp AS Time,
ae.actor AS User,
ae.action AS Action,
ae.action_value AS Value,
case when ae.action='tag write' then
(select oae.action_value from AUDIT_EVENTS oae where oae.action=ae.action and oae.action_target=ae.action_target and oae.EVENT_TIMESTAMP<ae.EVENT_TIMESTAMP order by oae.EVENT_TIMESTAMP desc LIMIT 1)
else '' end as oldValue,
ae.action_target AS Target,
ae.actor_host AS Host,
ae.status_code AS 'Status Code',
ae.originating_system AS 'System',
ae.originating_context
FROM AUDIT_EVENTS ae
ORDER BY
ae.audit_events_id DESC
This feature should be in it from the begining…
Is there a way to use this but still use Functions so I can filter what kind of tags I want to show in the table? For example, I currently use a date range component to filter the audit table in the client to a certain date and I use a textfield to type a name to filter the target.
But if I also want the old value to be shown with this query then I'm not able to use those functions any more.
I'm working with Vision.
It seems it would be easy for IA to update the tag value change audit to include the previous value in the audit log, to include it in the “Value” column; update it so “Value” column has the new, currentValue = newValue and previousValue = oldValue.
In any case, I think the previously shared SQL was for MySQL/PostgreSQL, this SQL works for SQL Server if the previous value exists… :
SELECT
ae.audit_events_id,
ae.event_timestamp AS [Time],
ae.actor AS [User],
ae.action AS [Action],
ae.action_value AS [Value],
CASE
WHEN ae.action = 'tag write' THEN (
SELECT TOP 1 oae.action_value
FROM AUDIT_EVENTS oae
WHERE oae.action = ae.action
AND oae.action_target = ae.action_target
AND oae.event_timestamp < ae.event_timestamp
ORDER BY oae.event_timestamp DESC
)
ELSE '' -- cast if needed, e.g. CAST('' AS varchar(100))
END AS oldValue,
ae.action_target AS [Target],
ae.actor_host AS [Host],
ae.status_code AS [Status Code],
ae.originating_system AS [System],
ae.originating_context
FROM AUDIT_EVENTS ae
ORDER BY ae.audit_events_id DESC;
It would not. Auditing is a tag actor, just like history. Tag actors get the new value/timestamp/quality as a QV and nothing else.
The previousValue available in scripting is synthesized by the scripting tag actor at the end of the pipeline.
(post deleted by author)
Thanks for the information. It seems like there could be an administrative setting that tells Ignition to log the currentValue and previousValue to the audit table when flagged tags change values, essentially automating logging/auditing for a Value Changed event vs. custom scripting I’ve done for logging the new currentValue and the previousValue.
Maybe not “easy”, though if it’s at the end of the pipeline means the previousValue is available? Why can’t that be automated via Ignition to be written to the audit table?
