Hello,
I want to extract certain events from the Event Chronicle SQL server on a regular basis. My intent is to execute a query and pull that data into another database for report processing. I have been using the Excel reporter tool to perform this function on a timed basis, but is is rapidly becoming unwieldy.
What are the best practices for accessing the Event Chronicle in this manner?
Thank you
Dave,
One method is to use the Scheduler in DeltaV Operate to execute a VBA script at fixed times that executes an SQL command and drops the data into the other database, either through an SQL INSERT or lets that database read the records sets as an xml or csv.
Example code for reading data out of the Event Chronicle using SQL is below where MY_PROPLUS is the name of your chronicle host, the structure of sSQLstatement needs to be changed to what you want to read, and the destination and format of the data file. The VBA needs a reference to Common Files\System\ado\msado60.tlb (Microsoft ActiveX Data Objects 6.0 Library). You'll need to do some testing to make sure the data retrieval is working correctly. I know this is not exactly all you need but hopefully it is enough to get started.
Cheers.
Private Sub GetAndSaveAlarmData()
'Tested in DeltaV version 11, 12 and 13
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Chronicle As String, A As String, sSQLstatement As String, sLogStart As String, sLogStop As String
Dim Filenumber As Integer, iTemp As Integer
On Error GoTo ErrorHandler
'Get offset from site local time to UTC used in the Event Journal
If IsDSTNow() Then
Time_to_UTC = 7 'Put your own offset to UTC here
Else
Time_to_UTC = 8 'Put your own offset to UTC here
End If
'Convert local time to GMT used in Event Journal
LogStartTime = CDbl(Now - 1 + (Time_to_UTC / 24))
LogStopTime = CDbl(Now + (Time_to_UTC / 24))
'Convert log start and stop times (in UTC not local) to strings to use in SQL Server command with custom funtion
sLogStartTime = fTimeToString(LogStartTime)
sLogStopTime = fTimeToString(LogStopTime)
'Connect to Event Journal database on the ProPlus from this workstation (use gs variable to get workstation name)
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _
"Data Source=MY_PROPLUS\DELTAV_CHRONICLE;" & _
"Initial Catalog = EJournal;" & _
"Use Procedure for Prepare=1;" & _
"Auto Translate=True;" & _
"Packet Size=4096;" & _
"Workstation ID=" & frsvariables.gs_wsName.CurrentValue & ";" & _
"Use Encryption for Data=False;" & _
"Tag with column collation when possible=False;" & _
"QuotedID=No"
conn.Open
'Assemble SQL command text string
sSQLstatement = "SELECT * FROM Journal WHERE Event_Type = 'ALARM'" & _
" AND State = 'ACT/UNACK'" & _
" AND Date_Time BETWEEN '" & sLogStart & "' AND '" & sLogStop & "'" & _
" AND Desc1 NOT IN ('HIGH','HIHI','LOW','LOLO','OPEN','CLOSE','RATE','UNDER','OVER','TURB HI','TRAVEL','CLOSED', 'CFN') ORDER BY Ord"
' Create a recordset object.
Set rs = New ADODB.Recordset
'Get data
With rs
' Assign the Connection object.
.ActiveConnection = conn
' Extract the required records.
.Open sSQLstatement
'Write data for file
Filenumber = FreeFile
Open System.PicturePath & "\Temp\alarm_log.txt" For Output As #Filenumber
Do Until rs.EOF
Print #Filenumber, rs.GetString(, 500000, Chr(9), Chr(13) & Chr(10), "")
Loop
Close #Filenumber
' Tidy up
.Close
End With
conn.Close
Set rs = Nothing
Set conn = Nothing
Exit Sub
ErrorHandler:
rs.Close
End Sub
In reply to RobPerry:
In reply to dave_marshall:
Hello again,
I am updating this question/discussion with the method that I ended up using in the event it may be helpful to others or to spawn a better discussion on the topic. Please note, I'm not a DBA!
To begin, a refresher on my goals when starting this project:
Youssef El-Bahtimy published an article entitled "Do not add custom SQL views, stored procedures, etc to your Event Chronicle, Batch Historian, or other DeltaV SQL databases...." (http://emersonexchange365.com/operateandmanage/deltav/f/50/t/4797) that provided some hints, but it wasn't detailed. When I combined this with Robs response above, I decided to take the approach outlined below.
My biggest challenge was properly configuring the Linked Server and the job to execute with permissions able to access the Event Chronicle. This is where the SPN/Kerberos issue came into play. I struggled for quite a while trying to get the Linked Server to properly pass the domain user credentials used to execute the job. My goal was to make no changes to the Event Chronicle SQL configuration, but I eventually gave in and created the local SQL user account.
Now that the link has been properly established, I can write SQL queries to get data from the Event Chronicle to my hearts content. As noted in Youssefs article, all my logic resides in my SQL server that I installed for reporting.
Thank you,
Dave
Andre Dicaire
In reply to Andre Dicaire: