This Logging & Trending quick start guide is the second in the series of Adroit Smart SCADA quick start guides. It follows on from the basic Adroit Smart SCADA Quick Start Guide which is an essential pre-requisite to being able to make use of and follow this second one. As with other quick start guides, it is not a substitute for the training material or in-built help but rather an additional resource to these

We start by gaining an understanding of the Adroit data logging stack We then install and configure SQL Server Express so that both variants of data logging – native and SQL can be explored. In order to have some live data to log and trend, we take the opportunity of creating a few instances of agent types that can readily generate suitable dynamically changing data

Next, we log these dynamically changing values firstly to proprietary Adroit log files, and secondly to SQL Express. We then verify data is being logged correctly by diagnostically retrieving logged values firstly using the Adroit Configurator and secondly with SQL Server Management Studio

Then we bulk-log a number of tags illustrating how this can be done in a single-click fashion

As a final step we create a Smart UI graphic form containing a LineChart control that seamlessly shows both historical and real-time data values, and run this form in Smart UI Operator

Before anything else, we need to understand something about Adroit’s data logging stack, shown in the diagram below:

Starting at the top-left of the diagram, the box labelled Logged Tags refers to tag values, such as Analogs and Digitals configured for data logging. These are the sources of logged data. Moving across is a box labelled Trend Controls that refers to charts and trends on SmartUI graphic forms. These are the consumers of logged data. Further to the right are Reports and Other logging sources & consumers which are really beyond the scope of this guide, other than to mention in passing that there are other mechanisms such as DBLog and DBAccess agents that are possibly more suitable for generating report data than DataLog agents are Moving down a level in the stack are Adroit Data Logging Agents. As implied by the diagram, these DataLog agents are solely responsible for carrying out all data logging tasks in Adroit, and are an example of employing the agent server object model,

with its extensible set of data types, as a means of implementing Adroit system functionality. Other examples of this kind of thing are to be found with Device and Scan agents involved in implementing the scanning sub-system, and Alarm, AlarmList, and AlarmManagement agents involved in implementing the alarming sub-system.

As the diagram shows, DataLog agents offer two major logging options:

• On the left of the diagram we see logging built on top of the ADO.NET stack, optionally involving OLE DB and ODBC on top of any ADO compliant relational database as a data repository
• To the bottom right of the diagram are native binary files used to store logged data

There are trade-offs associated with each approach: Native binary files are lightning-quick, and efficient in terms of disk space, but the data is stored in a proprietary format, meaning data can only be extracted using existing Adroit tools or custom software application. Logging to an industry-standard relational database, on the other hand, is a bit slower, and uses more disk space than proprietary format binary files, but the big advantage of this standardized approach is that logged data can be queried and reported on using any tool or application capable of executing an SQL query on a database

One fairly common approach is to log tags twice: optionally at a high rate, but relatively short-duration to native file(s), as well as at a slower rate but longer duration to SQL database

Using DataLog agents as the sole data logging interface in this way has huge transparency benefits in that log sources such as tags, and consumers such as trends do not need to know whether the repository is a database or a set of binary files

If you want to try out both forms of data logging, in addition to the basic Adroit installation carried out as part of the initial Quick Start guide, unless you already have a database installed on your PC or tablet, or have suitable access to one on your local Intranet, you will need to install SQL Server Express. This ships as part of the full Adroit Smart SCADA distribution which can be acquired as an ISO file from the following Adroit web page: https://adroit-europe.com/TechnicalResources Alternatively you may want to find a link out on the web, as SQL Express is a freely available from Microsoft

Just follow all the default options and choices through what is a fairly lengthy, but quite straightforward installation process. Make sure you select Windows Authentication at the point you are presented with this choice, as it ensures you can log on to SQL Server with your existing Windows security credentials, and serves to simplify the whole process

Once installed, locate Management Studio in the Microsoft SQL Server program group. It doesn’t really matter which version of SQL Server you install. The screenshot alongside shows SQL Server 2014 Management Studio installed on Windows 10

On first running Management Studio you need to connect it to a server For a default installation on the local machine the name will be machineName\SQLEXPRESS, VWEBDEV\SQLEXPRESS in the screenshot Click the Connect button, then right-click the Databases node in the tree-view that appears, and create a database called say AdroitLogging that we will use to store all logged Adroit data Just click OK and accept all the defaults in terms of database size, etc.

Before configuring any logging, we need to create some dynamically changing tags in Adroit so that we can see that they are logged and charted correctly. To this end, we will create and configure the following list of tags:

Start Smart UI Designer and open the agent server Configurator just as we did in the previous basic quick start guide. Select agent type Timer from the pull-down list of agent types. Create a new instance called TIMER_01 with a suitable description. Click the Add button to create TIMER_01 which, when Edited or double-clicked from the list of agents of type Timer, will pop up a dialog something like:

Clicking the Help button on the Timer agent pop-up will display the relevant page from the in-built Adroit help material as shown:

To configure TIMER_01 as a pulse generator with mark- and space-ratio of 5 seconds, set the Delay time and Pulse width values to 5000 as shown, and tick the Free running and Start timer options This should have the effect of causing TIMER_01.value to oscillate between one and zero every five seconds. We will confirm this after logging and trending the dynamic values later in this guide

Expression agents in Adroit are a way of combining many different tag values into arbitrarily complex mathematical and logical expressions. An Expression agent instance has a single value output or result, but can have up to twenty different input tags. Create an Expression agent instance called EXPR_01 using the Configurator just as you did previously for the Timer agent. On editing EXPR_01, the Expression agent configuration dialog will pop up as shown:

Here you can see place-holders for up to twenty input values V01 through V20, as well as, amongst other things, the value or result output (top left)

In order to create a sinusoidally varying value we can use one of Adroit’s in-built system tags, specifically SYSTEMINFO.second and put this through the mathematical sine function as shown below:

As you can see, input V01 or just V1 is used to hold the value of SYSTEMINFO.second, the current second number Because the Windows maths libraries use radians, not degrees we need to convert 0 - 60 seconds to 0 - 2π radians to make sure we get a full sine cycle every 60 seconds The expression to do this is sin(v1*2*π/60) as shown Finally, to make sure EXPR_01 calculates a new value every time the system second input value changes, we need to select the Continuous option from the Evaluation radio buttons bottom right. Configuring EXPR_01 this way should result in EXPR_01.value varying sinusoidally with a period of 60 seconds, i.e. every minute. Once again we will confirm this after logging and trending these dynamic values

Click the Help button on the Expression agent configuration dialog to get a lot more in-depth information about the scope and potential of Expression agents in Adroit

Create an Integer agent called INTEGER_01 and a Script called SCRIPT_01. There is no configuration to be done for INTEGER_01 as it is going to be just a place for SCRIPT_01 to store its result

Note: before being able to create a Script agent, you will need to tick the Advanced check-box on the main Configurator window. This enables you to see the more advanced agent types which, in the interests of simplicity, are not visible to you by default. On editing SCRIPT_01, you should see a dialog something like:

In configuring SCRIPT_01, we will make the following changes: As a preference, we change language from VBScript to JScript, since JScript is a widely-used, universal language, whereas VBScript is proprietary to Microsoft We use the button to add a file called say scripts.jav to the list of script modules, as shown below. Be sure to uncheck the Exclude path from file name check-box, since the default setting for this relies on relative paths, and is only approriate when moving the project around to different machines with possibly different folder structures Select Main as the Main subroutine by clicking the ellipsis button […] and trigger the subroutine to run every five seconds by ticking the Run every check-box

The configuration dialog for SCRIPT_01 should now look like…

The final step is to edit the actual JScript script which we do by clicking the button. This invokes a Script Editor in which you can edit your script…

As you can see function Main which we have scheduled to run every five seconds makes an Adroit.SetTag call to set tag INTEGER_01.value to a random value between 0 and 100. Close the Script Editor, saving changes to the script, and then OK the Script agent configuration dialog to run the script. Once again we should see random values when logging and trending have been done

To get more information on agent server scripting in Adroit, click the Help button on the Script agent configuration dialog. This will pop up the relevant section of the Adroit help material, detailing a list of methods supported by the Adroit script object

In terms of JScript and/or VBScript tutorial material, language references are available as compiled help modules (CHM files) at:

https://adroit-europe.com/TechnicalResources After acquiring one of these CHM files, when trying to open it, you will probably get a security warning as shown. Be sure to uncheck the check-box the first time you open the file in order to see all the content correctly:

At this point, having configured a Timer, an Expression, an Integer, and a Script, you should save your work by clicking File/Save on the main Configurator window

We’ll start by logging our sine expression EXPR_01 in proprietary, binary format as opposed to a SQL database. Select EXPR_01 in the list of Expression agents in the main Configurator window and click the Log button third from bottom on the right. This will pop-up a logging configuration dialog for EXPR_01 something like…

As you can see, there are many different logging configuration options, all of which are comprehensively explained in the Help material that can be accessed by clicking the Help button from this dialog The Datasource edit field in the middle, which is not fully visible contains the logging file name and by default this is the same as the tag database load file name – just different extension. This is OK just left as it is As indicated, the only things that therefore need changing are: a) The logging rate, or Time Deadband, from 10 seconds to something less than 1 second since our expression is changing every second, and b) The Start/Stop radio button group at the top, which starts and stops the tag logging

Before we get around to viewing logged data on a trend or chart, we can verify tag data is being logged successfully, just by clicking the Retrieve button as shown. By default, this retrieves all logged changes that have taken place in the last hour, as you can see at the bottom of the screen shot

Next, we’ll log our Integer and Timer tags, but this time to SQL Server Express instead of proprietary binary log file

We start in the same way by selecting INTEGER_01 in the list of Integer agents in the main Configurator window, and clicking the Log button At this point, before doing anything else, click the Advanced button on the logging configuration dialog and make sure the tick box highlighted in the screen shot alongside is checked What it does, is ensures that data values logged to SQL Express have a human-readable timestamp string and not just a machine-readable millisecond count since 1 Jan 1601. As this is an agent server global setting, you will probably need to restart the agent server for it to have any effect

To configure a tag for logging to SQL Server Express, we click the SQL button to the right of the Datasource edit field as shown This will pop-up a sequence of Microsoft Data Link Properties dialogs described in some detail below The effect of these dialogs is to produce an SQL connection string like the one shown in the screen shot alongside, and starting with: Provider=SQLOLEDB.1; Integrated… Before closing the logging configuration dialog, be sure to start INTEGER_01 logging by selecting the Start radio button, after setting the Time Deadband (logging rate). One second should be more than fast enough here, since we know our random number generation script only runs every five seconds If you want, you can click the Retrieve button to retrieve logged changes for this tag from SQL Express, but as we will see later you can also see logged values using SQL Server Management Studio

The Data Link Properties dialogs are shown below. First we need to select a Provider. Since we are logging to SQL Express we choose the Microsoft OLE DB Provider for SQL Server You may alternatively want to try SQL Server Native Client which might have less overhead than the OLE DB provider

The second dialog allows you to define the connection to the selected provider. As we mentioned previously when installing SQL Express, the server will be machineName\SQLEXPRESS or VWEBDEV\SQLEXPRESS in this case. We select Use Windows NT Integrated security as this was the installation option selected for SQL Express. Finally, we select the AdroitLogging database created previously after we installed SQL Express

If you like, you can test the connection by clicking the Test Connection button before closing the Connection tab

Configure Timer TIMER_01 for logging to SQL Express in the same way that you have just configured INTEGER_01 for SQL Express logging

In the previous quick start guide, we learnt how to grow the tag database very rapidly and productively by CSV-exporting, editing, and CSV-importing the extended file(s). This technique can also be used to grow the data logging configuration very rapidly by exporting, and subsequently importing agents of type DataLog

But an even simpler technique than this exists for bulk data logging configuration:

Start by logging Analog BAT_01_PMP_01_SPD, the first of our Pump Analogs created in the bulk configuration section of the previous quick start guide. Log for say 90 days at 1 second, and Start the data log agent, as shown alongside Next, go back to the main Configurator window, and multiply select the remaining 9 pump Analogs, as shown:

Now click the Log button, and you should see the last of the selected Analogs, BAT_02_PMP_05_SPD configured for logging with the same criteria as the initially selected one In fact, all of the multiply selected Analogs will have been log-configured in this way You can see this by returning to the main Configurator window, and selecting Type DataLog from pull-down list of agent types, as shown alongside So, by first establishing a set of logging criteria for a single logged tag, then multiply selecting a group of other tags, you can bulk-configure data logging with a single click

At this point it might be a good time to save your work again, by clicking File/Save on the main Configurator window

Start SQL Server Management Studio, and connect to machineName\SQLEXPRESS as previously. Navigate down the Databases tree node, opening up the AdroitLogging database as well as its Tables sub-node. In here you should see some tables relating to INTEGER_01 and TIMER_01 logged tags

Right-click, say, the INTEGER_01 table and click Select Top 1000 Rows from the Context Menu that is displayed. You should see something like the screen shot below…

In order to see most recently logged values first, add the line ORDER BY Timestamp DESC as shown for the Select Clause in the SQL Pane at the top-right of the above screen shot. Click on the button in the Management Studio toolbar to execute the SQL Query again returning the data in the sort order requested. Note: because of our global advanced logging setting, a fourth, human-readable TimestampStr column is returned as part of the dataset. The second column contains the randomly-changing Datavalue column for our INTEGER_01

Now that we are successfully logging tag values as they are dynamically changing, we are in a position to start trending these values on Smart UI graphic form controls

If it is not already running, start the Smart UI Designer by double-clicking the desktop short-cut. Create a new graphic form called, say, FORM_03

From the Toolbox, select and drag a LineChart control from the Data sub-tree, and drop it onto your graphic form as shown Re-size and re-position the control on your graphic form by dragging the sizing handles To be of any use, you will need to make it substantially bigger than the LineChart shown alongside

After dropping the chart onto your form you will be presented with configuration dialog as shown alongside If the dialog is closed, you can get back to it at any time by clicking the smart tag at the top right of the chart control… ...and selecting the Edit Chart link near the bottom...

Starting with the Series Setup, edit or Add Series for each different tag, choosing a different colour and title for each of the three different tags

Next, as shown below, click on the Values tab, and browse in the three different tags for each of the three different series, by clicking the ellipsis button […] alongside the Y Values edit field

	Specifically... - TIMER_01.value for the Pulse Train series - EXPR_01.value for the Sine series - INTEGER_01.value for the Random series

Because each of the three tags have different minimum and maximum values, it makes sense to have different vertical axes for each series

	So, choosing Axis Setup, and by clicking the plus [+] button, we create three custom axes: Custom 0, Custom 1, and Custom 2, with Minima and Maxima of 0 and 1,-1 and 1, and 0 and 100, respectively Custom 0 should also be made non-visible, as shown

Because we want to divide up the vertical axis between the three series, we need to adjust the custom axes positions as well

So, after selecting the Axis Position tab... Custom axis 2 takes the top one-third, starting at position 0 and ending at 33 percent, and also has a relative (horizontal) offset of -5 so as to be positioned slightly to the left Custom axes 1 and 0 take the bottom two-thirds, without any horizontal offset, as shown

Back on the Series Setup, we now need to ensure that the correct axis is selected for each series

Specifically... - Custom 0 for TIMER_01 Pulse Train series - Custom 1 for EXPR_01 Sine series - Custom 2 for INTEGER_01 Random series In addition, for the TIMER_01 Pulse Train, and INTEGER_01 Random series, we select the Stairs option as shown This is to ensure we see a square wave and not a saw-tooth since we know these tags, unlike the Sine expression, are undergoing discrete value changes as opposed to continuous variation

At this point you should be able to exit all chart configuration dialogs, save graphic form FORM_03, and preview it by clicking the green Run button midway across the form toolbar

There are a few other settings you may want to make to improve the cosmetic look-and-feel of the chart control:

Firstly, on Fill Effects Settings select the Margins tab and make the left margin 8 or something. This will permit the offset Custom 2 axis to be properly visible:

Next, on the Line Style setting, click the Advanced View button, bottom left and select a Width of 2 to make the chart lines a little bolder:

Previewing forms in Designer is all very well, but in reality, graphics forms will be shown at runtime in Smart UI Operator. To run Smart UI Operator, double-click the relevant short-cut on your Windows desktop. This will open a login window in which you can just enter the normal Windows user name and password in use when you installed Adroit. There is a check box that can be clicked allowing you to log in to Operator automatically next time you run it. This is a good option to select to avoid having to re-enter credentials every time

When initially running Operator, you will see an Enterprise Manager pane towards the top left of the Operator screen From this you double-click Form_03 in the Quick Start project to open the form in Operator, at which point you should see something like the screen shot below, once you have dismissed the Enterprise Manager pane by right-clicking and toggling Operator Mode or just typing Ctrl+O

As an exercise, add a LineChart control to the Form_02 graphic form we developed as part of the previous Quick Start Guide. On this chart you can show the Mixer Temperature analog as well the Batch Start and Batch Reset digitals

If you run a few batches, the resulting form displayed in Operator should look something like…

Please email any feedback or suggested improvements to support@adroit-europe.com