Emerson Exchange 365

Has anyone written a batch file or Visual Basic code for the background task scheduler to export alarms and events from Event Chronicle as a text file automatically?

I do not want to use DeltaV Analyse, Excel Reporter or purchase the Alarms and Events OPC Server to do this.

You can export what I want manually in Event Chronicle, but other than using "Sendkeys" in VB, I don't know to automate the process.

The data needs to be in text format for the next stage of processing, which has already been designed for a Honeywell DCS.

One of my colleagues got some help from Matrikon to do this a while ago, so it is possible.

• alarms
• text file
• Export
• event chronicle
• events

iam5113

• 13 Apr 2016 5:55 PM
• Cancel

5 Replies

• Youssef.El-Bahtimy

  

  • 13 Apr 2016 7:16 PM

  The event chronicle is a SQL database, so examples of SQL queries to get what you need are very prolific on the internet.
  A simple vbs/vba could suite your needs. I've used one before as a lightweight client for investigations. Some key ideas:
  
  1. Connection string - The following opens the connection where the invoking user has rights to read the event chronicle.
  SQLServer = "HOSTNAME\DELTAV_CHRONICLE" (where HOSTNAME is the event chronicle server name)
  SQLDB = "EJOURNAL"
  Set Connection = CreateObject("ADODB.Connection")
  Connection.Open "Provider=SQLOLEDB;Data Source=" & SQLServer& ";Database=" & SQLDB & ";Integrated Security = SSPI;"
  
  2. Command string - this will be the SQL query. You have to know something about the table structure in order to write this. Using SQL Server Management studio, you can view the table structure, but a simple "Select top 1000 * from dbo.Journal" will get you the first 1000 records and all fields. You will probably have to make a more complicated query to ensure the 'Ord' column value is used to determine whether a record has been exported already (rather than use date_time)
  
  Set Records = CreateObject("ADODB.RecordSet")
  CommandString = "yourSQLQuery"
  Set Records = Connection.Execute(CommandString)
  
  3. Iterate through header field names (optional)
  Set Fields = Records.Fields
  For intLoop = 0 To (Fields.Count - 1)
  strHeader = strHeader + Fields.Item(intLoop).Name + ","
  next
  
  4. Iterate through records
  While NOT Records.EOF
  ....(your code...may need to include iterate through fields)
  For intLoop = 0 To (Fields.Count - 1)
  strRow= strRow + Fields.Item(intLoop).Value + ","
  
  Records.MoveNext
  
  Wend
  
  5. Create text file for output. Look up the textstream object.
  
  6. Clean up
  Set Connection = Nothing
  Set Records = Nothing
  
  Obviously lots of holes here, but there is enough for you to get started. Good luck and let me know if you have more specific questions.

  • Cancel
• iam5113

  

  • 14 Apr 2016 9:35 AM

  In reply to Youssef.El-Bahtimy:

  I haven't done much SQL querying recently, but at least this sounds as if it might be a viable way forward. My colleague, who has written the text file processor, is much more up to speed on SQL, so I'll run it past him and see if we can use the info you have very kindly provided. I'm busy with an acceptance test this week, but I will be on the case again soon and will let you know how I get on. Thanks:-)

  • Cancel
• RobPerry

  

  • 14 Apr 2016 11:02 AM

  To add to Youssef's reply here is an example of VBA code that reads the alarms for a given period from the journal and drops the recordset data into a text file.
  The code requires a reference to Microsoft ActiveX Data Objects 6.0 Library (typically \Program Files\Common Files\System\ado\msado60.tlb).
  In conn.ConnectionString replace "MY_PROPLUS" with the name of your Event Chronicle host.
  In the SQL statement the format of the start and stop time strings are yyyymmdd hh:mm:ss (e.g. "20160414 06:30:00"). Don't forget the times in the chronicle are UTC so you need to make a correction from your local time.
  You can try different SQL statements to get the data you need, for example I add a condition starting with "AND Desc1 NOT IN ('HIGH','HIHI','LOW','LOLO', ..." to exclude process alarms from the list.
  
  Private Sub GetAndSaveAlarmData()
  Dim conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sSQLstatement As String, sLogStart As String, sLogStop As String
  Dim Filenumber As Integer
  
  On Error GoTo ErrorHandler
  
  'Connect to Event Journal database on the ProPlus (MY_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
  
  sSQLstatement = "SELECT * FROM Journal WHERE Event_Type = 'ALARM'" & _
  " AND State = 'ACT/UNACK'" & _
  " AND Date_Time BETWEEN '" & sLogStart & "' AND '" & sLogStop & "' ORDER BY Ord"
  
  ' Create a recordset object.
  Set rs = New ADODB.Recordset
  
  'Get data and write into delimited text file. Remember times are GMT on the Server
  With rs
  .ActiveConnection = conn ' Assign the Connection object.
  .Open sSQLstatement ' Extract the required records.
  Filenumber = FreeFile ' Write data for file
  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
  .Close ' Tidy up
  End With
  conn.Close
  
  Set rs = Nothing
  Set conn = Nothing
  
  Exit Sub
  
  ErrorHandler:
  Close #Filenumber
  rs.Close
  conn.Close
  frsHandleError
  
  End Sub

  • Cancel
• iam5113

  

  • 14 Apr 2016 1:14 PM

  In reply to RobPerry:

  Getting that VBA code working might be a bit easier. This forum could be my saviour. Thanks for the reply.

  • Cancel
• iam5113

  

  • 14 Apr 2016 5:11 PM

  In reply to RobPerry:

  I've got the VBA code working a treat. We may want to do a little bit more to get the data the way we want it, but I'm pleased I can now query the Event Chronicle database.

  • Cancel