cropped-header_image

SCADA Alarm History - 2/2

In the [first post](https://francotiveron.wordpress.com/2017/09/02/47/) I wrote on how to send alarm events in real-time from a Citect SCADA application to a remote MSMQ queue. In this one I will sho

September 3, 2017

In the first post I wrote on how to send alarm events in real-time from a Citect SCADA application to a remote MSMQ queue. In this one I will show the destination part of the complete application. Messages are fetched from the queue, filtered according to business rules, formatted as per the destination SQL server table, and finally inserted.

Few notes on SQL

Structured Query Language is probably the first programming language created to manage RDBMS(Relational Database Management System)s. SQL is one of those languages that have been able to resist all the innovation winds that programming has been battered with in the history.

Mainstream RDBMS vendors have adopted and adapted it to their needs, hence there is not an universal standard. Microsoft version is called TSQL (Transact SQL).

Along the way, many tentatives have been rolled out to abstract the access to SQL Server so to give a common library platform to application developers: native libraries, ODBC, DAO, ADO, various ORMs, until LINQ, just to remain in the Microsoft realm. Yet, all those approaches did never intend to replace SQL, but were instead built on top of it. LINQ queries are translated to TSQL before being submitted to the database engine.

Does this mean that a solid database related application should always rely on SQL when it comes to interact do the database? Well, this question would open to infinite debate, and it's not what this post is all about, so I'll give my opinion:

SQL is the "assembly" RDBMS language. Applications should be written using the best available technology, but with the best possible knowledge on how that technology works.

No new computer application is today written entirely in machine language (or its human readable form, assembly), but knowing that your high level language program is going to be translated to the CPU machine language and even better knowing how your CPU and chip-set work gives an edge and ultimately leads to a better high level code. Similarly, few computer applications are today written in C and use the native operating system API, but knowing that your java, C#, python and whateveryouwant modern language if ultimately translated into direct OS calls and even better knowing how your OS API works does ultimately leads to a better high level code.

Eventually, these are the main reasons why the artifacts in this post do not use plain SQL, but SQL server .NET integration, F# and LINQ.

Overview

The application component in this post is essentially a F# .NET DLL, hosted in the SQL server engine (named DT in the previous post). The logic operates in an infinite loop as follows:

  • Read the next message in the MSMQ queue
  • Convert the message to a custom record type
  • Filter the message contents against validation rules
  • Insert messages in the database table if validated, discard them otherwise

Solution Workflow

The steps here below are to be performed in the destination SQL server. The complete application requires to execute the first post workflow as well, though.

F#.NET NCF.AlarmHistory.DLL

This DLL exports a function as SQL stored procedure. Later in the post I show how to install it.

//Exports.fs

module NCF.AlarmHistory.Exports

open Microsoft.SqlServer.Server

[<SqlProcedure>] let ProcessCitectAlarmMessages() = Processor.processLoop() 0

The [<SqlProcedure>] .NET attribute marks this function as stored procedure, the compiler will add the proper meta-data to make it recognizable by SQL server.

Now the main processLoop

//Processor.fs module NCF.AlarmHistory.Processor

open System.Messaging open CitectAlarmEvent

[<Literal>] let private CitectAlarmHistoryQueuePath = @".\private$\NCFCitectAlarmHistory" let private queue = new MessageQueue(CitectAlarmHistoryQueuePath) queue.Formatter <- new XmlMessageFormatter([|"System.String"|]) let private msg () = string (queue.Receive().Body)

let rec processLoop () : unit = let m = msg() let log = Log.log m match m |> msg2record with | Ok evt -> DB.manage evt log | Error erMsg -> log erMsg if m <> "STOP" then processLoop()

The MSMQ queue is called NCFCitectAlarmHistory. It's a private queue in the target server. The message is expected to be a simple string, with fields separated by a tilde (~).

To be noted:

  • MessageQueue.Receive is used here, which is a blocking call. The thread will then sleep when no messages are available in the queue.
  • The infinite loop is implemented as a tail-recursive F# function.
  • The loop can be stopped by sending a plain "STOP" message to the queue (I normally use a trivial powershell command when i need); see GitHub for the actual code.
  • In case of error in the validation, an error message is logged in a different SQL table (full code in GitHub).

The message is then validated and converted to a record type value by the call to msg2record, that returns F# Result value, recently added to F# 4.1:

//CitectAlarmEvent.fs module NCF.AlarmHistory.CitectAlarmEvent

open System open System.Security.Policy

//Citect EventFmt = zone~{TAG,20}{NAME,50}{DESC,50}{STATE,10}{AREA,3}{PRIORITY,3}{DATEEXT,10}{TIME,11}{ONDATEEXT,10}{ONTIME,11}{OFFDATEEXT,10}{OFFTIME,11}{ACKDATEEXT,10}~{ACKTIME,11} type State = On | NotOn type CitectAlarmEvent = { zone : string tag : string name : string desc : string state : State area : int16 priority : byte time : DateTime timeOn : DateTime timeOff : DateTime option timeAck : DateTime option }

[<Literal>] let private separator = '~'

let private split (msg : string) = msg.Split(separator)

//tag, event time and timeOn must exist for a message to be valid let msg2record (citectMessage : string) : Result<CitectAlarmEvent, string> = let mutable t = DateTime.Now let mutable tOn = DateTime.Now let parsedt t d = let r, dt = DateTime.TryParse(t + " " + d) in if r then Some dt else None

match split (citectMessage.ToUpper()) |> Array.map (fun s -> s.Trim()) with | [|zone; tag; name; desc; state; area; priority; date; time; dateOn; timeOn; dateOff; timeOff; dateAck; timeAck|] when not (String.IsNullOrWhiteSpace(tag)) && (match (parsedt time date) with | Some dt -> t <- dt; true | None -> false) && (match (parsedt timeOn dateOn) with | Some dt -> tOn <- dt; true | None -> false) -> Ok { zone= zone tag = tag name = name desc = desc state = match state with | "ON" -> On | _ -> NotOn area = System.Int16.Parse(area); priority = System.Byte.Parse(priority) time = t timeOn = tOn timeOff = parsedt timeOff dateOff timeAck = parsedt timeAck dateAck} | _ -> Error "Invalid Format"

The coming message is expected to fulfill some business rules (see the when clause in the pattern matching code), otherwise an error is returned. If the conversion succeeds, the new record is returned and processed by the DB.manage function:

//DB.fs module NCF.AlarmHistory.DB

open System open FSharp.Data.TypeProviders open CitectAlarmEvent

//type private dbSchema = SqlDataConnection<" Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=CitectAlarms;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"> type private dbSchema = SqlDataConnection<"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='C:\Users\franco.tiveron\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\CitectAlarms.mdf';Integrated Security=True;Connect Timeout=30"> let private db = dbSchema.GetDataContext() let private conn = db.Connection conn.ConnectionString <- "context connection=true" conn.Open()

let private saveEvent (e:CitectAlarmEvent) = let r = new dbSchema.ServiceTypes.NCFEvent( Tag = e.tag, Name = e.name, Desc = e.desc, Location = "cmoc/npm/" + e.zone + "/" + (string e.area), Priority = e.priority, TimeOn = e.timeOn, TimeOff = e.timeOff.Value, Duration = (float32 (e.timeOff.Value - e.timeOn).TotalSeconds) ) db.NCFEvent.InsertOnSubmit(r)

//Recordable events are OFF with event time >= OffTime and OffTime > OnTime let **manage **(e:CitectAlarmEvent) (logger: string->unit) = match e.state, e.time, e.timeOn, e.timeOff with | On, _, _, _ -> () | NotOn, t, tOn, Some tOff when t >= tOff && tOff > tOn -> if query {for event in db.NCFEvent do select (event.Tag, event.TimeOn, event.TimeOff) contains (e.tag, tOn, tOff)} then () else saveEvent e | _ -> logger "Invalid Event"

try db.DataContext.SubmitChanges() with | x -> logger x.Message

let quarantine message reason = let nr = new dbSchema.ServiceTypes.NCFQuarantinedMessage(Message = message, Reason = reason) db.NCFQuarantinedMessage.InsertOnSubmit(nr) db.DataContext.SubmitChanges()

Important to note:

  • To ease the development at design time, the SQL Data Provider is used (see SqlDataConnection).
  • At run-time, though, the connection string is changed to the contextual ("context connection=true"); according to the documentation, this gives the better run-time performances, as the connection is managed internally by the SQL server engine.
  • To avoid duplicate alarm records (Citect servers can be restarted at any time and when they do the full cache is re-sent to the queue, so duplicate messages are in order), the target table is queried using LINQ and the handy F# query expression.

Deploy NCF.AlarmHistory.DLL to SQL Server

To run CLR code in SQL Server, the DLL assembly must be registered with SQL Server, along with all the possible dependencies. SQL Server 2014 has most of the .NET framework 4 registered already, but not all the dependencies needed for our DLL. Here the SQL script to be run (as you can see, knowing the "assembly" always comes useful):

//Deploy.sql /* MUST BE RUN IN THE TARGET SQL SERVER INSTANCE, DATABASE CitectAlarms MUST EXIST Files must be copied first (deploy.cmd) */

/Dependencies (once)/ sp_configure 'clr enabled', 1 go reconfigure go

alter database CitectAlarms set trustworthy on

use CitectAlarms

CREATE ASSEMBLY [FSharp.Core] FROM 'C:\NCF\FSharp.Core.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY [System.Messaging] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY [System.Data.Linq] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Data.Linq.dll' WITH PERMISSION_SET = UNSAFE GO

/* Package (after any change) */ use CitectAlarms

IF EXISTS (SELECT * FROM sys.procedures procs WHERE procs.name = N'ProcessCitectAlarmMessages') DROP PROCEDURE ProcessCitectAlarmMessages GO

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'NCF.AlarmHistory') DROP ASSEMBLY [NCF.AlarmHistory] GO

CREATE ASSEMBLY [NCF.AlarmHistory] FROM 'C:\NCF\NCF.AlarmHistory.dll' WITH PERMISSION_SET = UNSAFE GO CREATE PROCEDURE ProcessCitectAlarmMessages AS EXTERNAL NAME [NCF.AlarmHistory].[NCF.AlarmHistory.Exports].[ProcessCitectAlarmMessages] GO

The first part can be run only once if the dependencies do not change, while the second part (marked by /* Package (after any change) */) must be rerun every time our assembly is rebuilt.

Database Schema Last but not least, here the SQL script to create the necessary database objects.

The Visual Studio project is available on GitHub. Visual Studio 2017 community has been used for this development.

Conclusion

This post ends the 2-posts series "SCADA Alarm History"; the application is running in production since about a month and proved to be stable. We are beginning to build excel add-ins and Power BI reports on top of it.

Full fledged solutions are available in the market to do the job, but:

  • they introduce additional capital licensing and operational maintenance costs
  • they can require a significant time and cost effort to be successfully integrated in existing infrastructures
  • They need anyway a customization work to be carried out to reflect the specific business requirements and customs

The solution proposed here addresses these points:

  • No licensing costs, as all the resources used are already available or free.
  • Being a fully owned application, maintenance can be managed internally, which usually has significantly lower costs.
  • Owning the source code gives the maximum flexibility, allowing to adapt it to the specific business infrastructure.
  • Customization is fully open as we have a business owned SQL database. Data can be put on spreadsheet seamlessly (without sometimes annoying export workflows) and reporting can be managed by what we want. I have serious doubts that any of the third-party packages have better reporting features than many open source or much cheaper commercial frameworks (like Power BI for example).