Logging Smarter - Dynamic Table Selection at Runtime

6 min read

Oct 26, 2017 2:00:00 PM


Does your data logging software force you to define the location where you're logging your data ahead of time?  Wouldn’t it be nice if your logging software could evaluate your data and make a decision on where the data should be logged?

In this first of two posts in a series on dynamic SQL logging, I'll show you how the OPC Data Logger can easily be configured to switch between SQL Tables at runtime, reducing any post-log sorting you have to do in SQL and saving you time and effort in the process.

Smarter Data Logging Software Can Choose Locations

Don’t let your data logging software dictate where you can log your data.  How do you currently handle situations where data should ideally be logged to different destinations (Database/tables/CSV files/etc) based on some defined variable/condition of the current process being monitored?

Typically, we see users either logging all data to the same destination and sorting takes place after the fact, or many different projects must be created and the logger configuration is switched out whenever a new logging destination is need.  Neither of those methods is ideal and requires substantial additional effort to get the desired results.

Did you miss our previous post on conditional logging?

With those less than ideal options in mind, I now want to show you how the OPC Data Logger can easily be configured to switch between three SQL Tables in the following two example logging scenarios and help organize logging chaos right from the start:

  1. I want to log my product density at varying temperatures, and want my data to be logged in 3 ranges; densities at temperatures up to 100 °C should be logged to a ‘Low Temperature’ Table, densities at temperatures between 100 °C and 200 °C should be logged to a ‘Mid Temperature’ table, and densities at temperatures greater than 200 °C should be logged to a ‘High Temperature’ table.

  2. I want to log data to tables based on what product is currently being produced. I.e. Weights for Product1 will get logged to a ‘Product 1’ table, weights for Product2 to a ‘Product 2’ table, and weights for Product3 to a ‘Product 3’ table.

This first blog post will specifically address the first scenario and you can subscribe to our blog to make sure you are notified of the second post in the series that will address the second scenario.

Example Scenario 1 – Density Logging

For this scenario, I want to configure the OPC Data Logger with three separate Logging Tasks:

  1. A task for the low temperature range and configured to log to the low temperature table

  2. A task for the mid temperature range and configured to log to the mid temperature table

  3. A task for the high temperature range and configured to log to the high temperature table

Screenshot - Tasks For Logging Based on Temperature Range
If I were to run the project above, I would end up with all data getting logged to all three tables at the same time – not at all what I want – so how do I make sure that I'm only logging the correct data to only its corresponding table?

With triggers! Through the use of Monitored Item triggers in my OPC Data Logger configuration, I will be able to monitor the current Temperature value and enable/disable Logging Tasks based on the current temperature range.

Three triggers will be needed:

  1. LowTemperatureTrigger – configured to fire whenever the temperature is less than 100 °

    Screenshot - Low Temp Range Monitored Item Trigger

  2. MidTemperatureTrigger – configured to fire whenever the temperature is greater than or equal to 100 °C but less than 200 °C

    Screenshot - Mid Temp Range Monitored Item Trigger

  3. HighTemperatureTrigger – configured to fire whenever the temperature is greater than or equal to 200 °C

    Screenshot - High Temp Range Monitored Item Trigger

These monitored item triggers will evaluate the value of the CurrentTemperature tag, and the appropriate trigger (based on the range conditions I set) will fire.

Now all that remains is to associate the triggers with the appropriate Logging Groups. So I'll open the Sensor1Data Group configured under my Logging Tasks, and go to the Trigger Tab.

  1. For the Low Temperature group, I want the group to be active and logging whenever the conditions for the LowTemperature trigger are met, and be inactive when out of the low temperature range:

    Screenshot - Triggers for Low Temp Range Logging Group

  2. Similar to the Low Temperature Group, I need to add triggers that control the transition into and out of the temperature range. Because I need to worry about both the low-to-mid transition as well as the mid-to-high transition, all three triggers will need to be added to this logging group:

    Screenshot - Triggers for Mid Temp Range Logging Group

  3. The high temperature range group – similar to the Low Temperature range - only has a single transition to worry about. All three triggers should be added, but only the HighTemperature trigger can start logging for the group.

    Screenshot - Triggers for High Temp Range Logging Group

At this point, I can go ahead and run the project. The triggers will ensure that the logging groups are only activated once the temperature enters the range specified by the Monitored Item trigger.

Similarly these triggers can be utilized to create a variety of dynamic logging scenarios depending on your process.

Example Scenario 2 – Logging by Product

To be continued...

Don't forget to subscribe to the blog for my next post in this series on dynamically changing SQL tables based on the type of product being produced.

As access to any and all data at a process and enterprise level continues to include more and more sources, analyzing data as efficiently as possible is incredibly important due to the sheer volume of data available. Having to logging everything to one place and then sort the data for relevance is incredibly inefficient and your time could be spent more effectively on other tasks.

Interested in trying for yourself? Download the free OPC Data Logger trial to try dynamic logging with your own OPC data sources.

Get Your Free Trial of OPC Data Logger

Marc Holbach
Written by Marc Holbach

Software Toolbox Technical Blog

We're engineers like you, so this blog focuses on "How to" appnotes, videos, tech team tips, product update announcements, user case studies, and other technical updates.  Subscribe to updates below. Your feedback and questions on posts are always welcomed - just use the area at the bottom of any post.

Subscribe to our Blog

Recent Posts

Posts by Topic

See all