Tag Value change event log in audit_events table

dhananjay_vichare · October 28, 2014, 12:02pm

I am trying to log the tag value in the “audit_events” table, while the tag value change. I am trying us the tag event > Value change. Please advice.

dhananjay_vichare · October 28, 2014, 1:08pm

I wrote and checking the below code in script playground getting error : Please advice

currentDatetime = system.tag.read("[System]Gateway/CurrentDateTime")
CurrnetUser = system.tag.read("[System]Client/User/Username")
Hostname = system.tag.read("[System]Client/Network/Hostname")
TagPath = ‘FQ_ZEROED_EVENT’
action_name = ‘tag write’
tag_value = ‘zero’ #currentValue.value
statuscode = “0”
project = “project=WKWD”
originator = “2”
query = “SELECT max(AUDIT_EVENTS_ID) FROM audit_events”
rownumber = system.db.runScalarQuery(query)
rownumber1 = rownumber + 1
#insertQuery = “INSERT INTO event_test (Tagpath, Description, user, value1) VALUES (?,?,?,?)”, [Hostname, project, project, project]
#system.db.runPrepUpdate(“INSERT INTO inventory (name,description) VALUES (?,?)”,[name, description])
#system.gui.messageBox(str(insertQuery),“test”)
system.db.runPrepUpdate(“INSERT INTO event_test (AUDIT_EVENTS_ID, EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, ACTION_VALUE, STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?,?)”,[rownumber1,currentDatetime.value,CurrnetUser.value,Hostname.value,action_name,TagPath ,tag_value,statuscode, project, originator])

Error:

Traceback (most recent call last):

File “”, line 16, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO event_test (AUDIT_EVENTS_ID, EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, ACTION_VALUE, STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?,?), [647, Tue Oct 28 18:33:19 IST 2014, admin, auto6, tag write, FQ_ZEROED_EVENT, zero, 0, project=WKWD, 2], , , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO event_test (AUDIT_EVENTS_ID, EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, ACTION_VALUE, STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?,?), [647, Tue Oct 28 18:33:19 IST 2014, admin, auto6, tag write, FQ_ZEROED_EVENT, zero, 0, project=WKWD, 2], , , false, false)
caused by GatewayException: SQL error for "INSERT INTO event_test (AUDIT_EVENTS_ID, EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, ACTION_VALUE, STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?,?)": Unknown column 'AUDIT_EVENTS_ID' in 'field list'
caused by MySQLSyntaxErrorException: Unknown column 'AUDIT_EVENTS_ID' in 'field list'

Ignition v7.7.2-rc1 (b2014102314)
Java: Oracle Corporation 1.7.0_67

JordanCClark · October 28, 2014, 1:29pm

AUDIT_EVENTS_ID is your primary index and is set to auto increment whenever you insert a row. So, you don’t need to insert a value here.

Remove the references to the AUDIT_EVENTS_ID, and you should be okay.

dhananjay_vichare · October 28, 2014, 1:48pm

Thank J Clark,

I have tried as per your comment, the same error showing for ‘EVENT_TIMESTAMP’ column. Please advice.

Could I missing the quote for timestamp field. I have tried with quote also like (’?’).

Thank in advance.

Code :
currentDatetime = system.tag.read("[System]Gateway/CurrentDateTime")
CurrnetUser = system.tag.read("[System]Client/User/Username")
Hostname = system.tag.read("[System]Client/Network/Hostname")
TagPath = ‘FQ_ZEROED_EVENT’
action_name = ‘tag write’
tag_value = ‘zero’ #currentValue.value
statuscode = “0”
project = “project=WKWD”
originator = “2”
query = “SELECT max(AUDIT_EVENTS_ID) FROM audit_events”
rownumber = system.db.runScalarQuery(query)
rownumber1 = rownumber + 1
#insertQuery = “INSERT INTO event_test (Tagpath, Description, user, value1) VALUES (?,?,?,?)”, [Hostname, project, project, project]
#system.db.runPrepUpdate(“INSERT INTO inventory (name,description) VALUES (?,?)”,[name, description])
#system.gui.messageBox(str(insertQuery),“test”)
system.db.runPrepUpdate(“INSERT INTO event_test (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?)”,[currentDatetime.value,CurrnetUser.value,Hostname.value,action_name,TagPath ,tag_value,statuscode, project, originator])
Error:
Traceback (most recent call last):

File “”, line 16, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO event_test (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [Tue Oct 28 19:12:58 IST 2014, admin, auto6, tag write, FQ_ZEROED_EVENT, zero, 0, project=WKWD, 2], , , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO event_test (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [Tue Oct 28 19:12:58 IST 2014, admin, auto6, tag write, FQ_ZEROED_EVENT, zero, 0, project=WKWD, 2], , , false, false)
caused by GatewayException: SQL error for "INSERT INTO event_test (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?)": Unknown column 'EVENT_TIMESTAMP' in 'field list'
caused by MySQLSyntaxErrorException: Unknown column 'EVENT_TIMESTAMP' in 'field list'

Ignition v7.7.2-rc1 (b2014102314)
Java: Oracle Corporation 1.7.0_67

Pat · October 28, 2014, 2:16pm

Instead of using the Gateway/CurrentDateTime try using NOW() in the query instead, that way you won’t have to format the Gateway/CurrentDateTime.

VALUES (NOW(),?,?,?,?,?,?,?,?)",[CurrnetUser.value,Hostname.value,action_name,TagPath ,tag_value,statuscode, project, originator])

dhananjay_vichare · October 29, 2014, 5:23am

Thanks Pat and JordanCClark,

Thanks for the date time format suggestion.
I found that the table name is also wrong, which was not having ‘AUDIT_EVENTS_ID’ column. It was my silly mistake!

JordanCClark · October 29, 2014, 10:10am

[quote=“dhananjay vichare”]Thanks Pat and JordanCClark,

Thanks for the date time format suggestion.
I found that the table name is also wrong, which was not having ‘AUDIT_EVENTS_ID’ column. It was my silly mistake! [/quote]
I hate it when I do that! Glad you got it sorted out!

dhananjay_vichare · October 29, 2014, 11:01am

I am using below code in Tag event script (Value change script), the same script working in script play ground and event will log in to the Audit_Events table. When I am changing the tag value event not log in the table.
Also I have tried the insert the user name and Host name in the table, it is omitting null values and in project console it is showing error.

Also I observed that, if I change the value from designer or client event fired. If I change the value from PLC script won’t run and log the event.

Could you please help on this?

I want to log the tag value in the Audit_event table, Whenever tag value changed the event should log the current value into the event log table.

Code :
#This Script will log the event in the Audit_Events Table when tag value will changed.
CurrnetUser = ‘Dhan’#system.tag.read("[System]Client/User/Username")
Hostname = ‘Au61’ #system.tag.read("[System]Client/Network/Hostname")
CurProject = ‘XXXX’#system.tag.read("[System]Client/System/ProjectName")
TagPath = ‘Call’
action_name = ‘tag write’
tag_value = ‘3’
statuscode = “0”
project = “Project =” + CurProject
originator = “4”
system.db.runPrepUpdate(“INSERT INTO test.audit_events (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT,ACTION_VALUE) VALUES (now(),?,?,?,?,?,?,?,?)”,[CurrnetUser,Hostname,action_name,TagPath,statuscode, project, originator,tag_value],‘Mysql’,getKey=0,skipAudit=0)

JordanCClark · October 29, 2014, 3:10pm

Is there anything in the Tag Diagnostics?

dhananjay_vichare · October 30, 2014, 12:40pm

Thanks for the update.

I had checked the tag diagnostic and it is fine. Please see attached tag diagnostic window snapshot.

Still events are not log in to the ‘AUDIT_EVENTS’ table. Could you please confirm that we can log the event data by the script in to the default Ignition table ‘AUDIT_EVENTS’?

If no, is there alternate way to identify the tag value changed and get the event in to ‘AUDIT_EVENTS’ table.

Also Please find below tag event script snapshot and latest code.
Code:
def temp1():
print missedEvents
if initialChange == False:
print “DMV”
if currentValue.value:
print “TTPL”
#This Script will log the event in the Audit_Events Table when tag value will changed.
CurrnetUser = ‘Dhan’#system.tag.read("[System]Client/User/Username")
Hostname = ‘Auto61’ #system.tag.read("[System]Client/Network/Hostname")
CurProject = ‘WKWDF’#system.tag.read("[System]Client/System/ProjectName")
TagPath = “[.]T21”
action_name = ‘tag write’
tag_value = ‘3’
statuscode = “0”
project = “Project =” + CurProject
originator = “4”
print TagPath
system.db.runPrepUpdate(“INSERT INTO audit_events (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT,ACTION_VALUE) VALUES (now(),?,?,?,?,?,?,?,?)”,[CurrnetUser,Hostname,action_name,TagPath,statuscode, project, originator,tag_value],‘Mysql’,tx=temp1,getKey=0,skipAudit=0)

dhananjay_vichare · October 30, 2014, 12:43pm

Please note that I am using[b] Ignition 7.7.2 RC1 which is latest version (development version) and JAVA 8.20.

[/b]Is there any issue for tag event scripts.

Thanks in advance.

Pat · October 30, 2014, 1:09pm

Ahh…it’s because you’ve defined a function within a function without a call to that function. Remove the def temp1(): and change all your indents. But if you plan on using your code for multiple Value Changed events I would put your def temp1 code in a Gateway script and call that function from the Value Changed event.

dhananjay_vichare · October 30, 2014, 2:22pm

Hi Pat,

Thanks.

I am using temp1 as a global variable for runPrepUpdate query tx parameter (Refer marked in Red color in below code). I would like to know is there any error in the below code, resulting I am not able to log the values in the “audit_events” table.

Thank you in advance.

Code:
system.db.runPrepUpdate(“INSERT INTO audit_events (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT,ACTION_VALUE) VALUES (now(),?,?,?,?,?,?,?,?)”,[CurrnetUser,Hostname,action_name,TagPath,statuscode, project, originator,tag_value],‘Mysql’[color=#FF0000],tx=temp1[/color],getKey=0,skipAudit=0)

Pat · October 30, 2014, 3:12pm

The Transaction Identifier is a string variable so it would be - tx='temp1' Also, since you are not requesting the function to return a key or to skip the audit system you don’t need - getKey=0, skipAudit=0
Start simple, if using the project’s default database try -

system.db.runPrepUpdate("INSERT INTO audit_events " \ "(EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, " \ "STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT, ACTION_VALUE) " \ "VALUES (now(),?,?,?,?,?,?,?,?)", [CurrnetUser, Hostname, action_name, TagPath, statuscode, project, originator, tag_value])
If you’re not using the project’s default database try -

system.db.runPrepUpdate("INSERT INTO audit_events " \ "(EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, " \ "STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT, ACTION_VALUE) " \ "VALUES (now(),?,?,?,?,?,?,?,?)", [CurrnetUser, Hostname, action_name, TagPath, statuscode, project, originator, tag_value], database='Mysql')
I don’t know exactly what the Transaction Identifier does. I’m not sure if it’s designed to signify status in the Audit Log or cause the query to run in a type of thread. But once you get one of the above queries working then try adding tx=‘temp1’ to the function.

dhananjay_vichare · October 30, 2014, 3:53pm

Hi Pat,

Thank you,

I have tested and it working now, thanks.

Now I want to insert the USER NAME, HOST name and event Tag path. I am using below code. I am getting logged null value for marked in red color.

Could you please update me?

Thank you in advance.

Code:
#This Script will log the event in the Audit_Events Table when tag value will changed.
print missedEvents
if initialChange == False:
print “DMV”
if currentValue.value:
[color=#FF0000]CurrnetUser = system.tag.read("[System]Client/User/Username")
[/color] [color=#FF0000]Hostname = system.tag.read("[System]Client/Network/Hostname")
[/color] [color=#FF0000]CurProject = system.tag.read("[System]Client/System/ProjectName")[/color]
print CurProject.value
TagPath = [color=#FF0000]‘event.tagPath.itemName’
[/color] action_name = ‘tag write’
tag_value = ‘currentValue.value’
statuscode = “0”
project = "Project = "# + CurProject.value
originator = “4”
system.db.runPrepUpdate("INSERT INTO audit_events "
"(EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, "
"STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT, ACTION_VALUE) "
“VALUES (now(),?,?,?,?,?,?,?,?)”,
[CurrnetUser.value, Hostname.value, action_name, TagPath,
statuscode, project, originator, tag_value])

Pat · October 30, 2014, 4:12pm

If this is the code under the Value Changed function then you won’t have access to the client tags since this function is run under the gateway, not in a client.

The TagPath assignment should be - TagPath = tagPath

dhananjay_vichare · October 30, 2014, 4:24pm

Hi Pat,

Thank you,

Is there any alternate method to get the User and Host name? Using client script function and call that function in the tag change script.

Thank you in advance.

dhananjay_vichare · October 30, 2014, 4:33pm

Hi Pat,

I tried with the below code getting error mark on tag script. Please refer snapshot.

I am UDTs for tags.

Code:
#This Script will log the event in the Audit_Events Table when tag value will changed.
print missedEvents
if initialChange == False:
print “DMV”
if currentValue.value:
CurrnetUser = system.tag.read("[System]Client/User/Username")
Hostname = system.tag.read("[System]Client/Network/Hostname")
CurProject = system.tag.read("[System]Client/System/ProjectName")
print CurProject.value
[color=#FF0000]TagPath = tagPath[/color]
action_name = ‘tag write’
tag_value = ‘currentValue.value’
statuscode = “0”
project = "Project = "# + CurProject.value
originator = “4”
system.db.runPrepUpdate("INSERT INTO audit_events "
"(EVENT_TIMESTAMP, ACTOR, ACTOR_HOST, ACTION, ACTION_TARGET, "
"STATUS_CODE, ORIGINATING_SYSTEM, ORIGINATING_CONTEXT, ACTION_VALUE) "
“VALUES (now(),?,?,?,?,?,?,?,?)”,
[CurrnetUser.value, Hostname.value, action_name, TagPath,
statuscode, project, originator, tag_value])

Pat · October 30, 2014, 4:50pm

If you have auditing set up for your project (Project>Properties | Project>General>Auditing Settings) then Ignition will already log all that information when a user changes a tag in a client.

Generally the Value Changed function on a tag is used for when the value of a tag gets changed outside of Ignition or for some other special purpose, I don’t think many people would be using it to add entries into the audit table since that is generally handled by Ignition itself. So I’m wondering why you’re using the Value Changed function to log the information instead of letting Ignition handle it itself?

dhananjay_vichare · October 31, 2014, 5:10am

Hi Pat,

Thank you,

Yes I have configured the Enable Auditing from project property.

I have observed that, we are getting events only for operations performed from Designer or Vision Client like button pressed or Auto/Manual Sel. Switch selection changed or set point changed. But we could not find any provision in Ignition if we want to log the event such as pump is started or stopped (Run Status Bit in PLC). In short, tag value updated from PLC it is not logged in the event audit log.

We have around 600 tags like these (like Pump running (DI from PLC 0=Stopped and 1=Running), Intrusion alarms (DI from PLC 0 = Unarmed and 1 = armed) for which we would like to log the event.

Is there any simple method to log the tag change events in ignition?

I had attached Event log image of Wonderware application and Ignition application also.
We have prepared event log in Ignition as per the wonderware application. In the Ignition event log we have found missing pump running, Intrusion like events which was directly triggered from Digital Input.

Please let me know your views on event log.