Data Logging using node red

Hi team,

I am using Groov-Epic PR1 to read the modbus data over TCP/IP.

Right now i read total 20 tags from the 2 devices and my response is satisfactory and further to this i will logged my data in SQL Server with logging interval of 1 minute using node-red.

Now, my further requirement is to read 20 tags each from total 50 devices(total 1000 tags) with logging interval of 1 minute, can we achieve this using Epic PR-1 ??

Given that your logging interval is one minute and you’re not reading over 1000 tags, this should be doable with Node-RED – but if you start to pick up more tags or reduce the interval, then it’s pretty likely that performance will go down from there.

If you are expecting to add more tags down the line, log them more often, or need any features like store-and-forward when the network is down, check out Inductive Automation’s Ignition software. EPIC supports Ignition EDGE and with the 1.4 update you can enable the full Ignition Gateway to use their SQL bridge module to push tags straight into your database really cleanly. This might be a good option worth exploring for your project.

If you’re going to stick to the 1000 tags and 1 minute interval and want to use Node-RED, my advice would be to build up the SQL insert statement using one long string variable, then write them all at once at the 1 minute mark so that you can avoid opening and closing the database connection a thousand times every minute.

Do you know if the 20 tags from the 50 devices is the ultimate size of the application or could there be some changes down the line?

Hello,

Thanks for the response…
This is my maximum limits of tags,there could be reduced some tags as well as reduce the time interval too…

Node-RED is capable of faster scan rates, but with hundreds up to a thousand tags every scan you’ll start to see it struggle to keep up with the volume of data, I’d recommend looking into how Ignition might work for you.

One other thing to keep in mind is the lag from the network when you write to the database, how quickly do you need the data to be available in the database and where is your SQL server running? an on-premise server or in the cloud?
Depending on your needs and the state of your network, you might benefit from running the database on the EPIC itself to avoid going out over the local / web connection at all. If that sounds like it has potential check out MariaDB on EPIC, if not, no worries you can always use whatever database you already have in place.

@torchard’s advice on making one insert statement is very valid.

Check out this thread for the pitfalls of opening and closing a lot of connections;

1 Like

Thank you and noted…

Seems this information is very helpful for me for my upcoming application…

hlo harsil.s1
can you please tell me how to send modbus tcp data to ms sql

Hello @thakurshubham319,

There are many nodes available on node red,you can simply connected modbus tcp/ip node with ms sql node.

thank you
can u please share any sample or web link to solve this issue.
i have tried so much but did not get success.

Hi @thakurshubham319,

You can find the video as per attached below link for configuration of your requirement.
Hope this will help you to get out of this.

https://www.opto22.com/support/resources-tools/videos/video-node-red-get-data-from-pac-to-ms-sql

thank u harshil.s1
it helpd me alot
but i have data in form of holding registers like 40001,40002 etc in modbus tcp
i am not able to insert this data to sql directly.
how can i convert this holding register data in form of local tags as shown in video.

Hi @thakurshubham319

There are two ways of achieve this.
You can use direct tcp node and also you can take this holding register value into pac control and there after you can use pac control node.

@harsil.s1 thank u sir
i am using modbus tcp directly…so is it possible to send data directlly to ms sql or its compulsory to use pac control system.
how can i use direct tcp node.

Yes it is possible to do it all in Node-RED and not go though PAC Control.
What have tried?
Got a screen shot of your flow we could take a look at?

@harsil.s1 thank u sir

this is tha flow that i am using…
code given below

[{“id”:“1d060b.2739c9f6”,“type”:“tab”,“label”:“Flow 1”,“disabled”:false,“info”:""},{“id”:“380a7e52.021592”,“type”:“modbustcp-server”,“z”:"",“name”:“poll”,“host”:“127.0.0.1”,“port”:“502”,“unit_id”:“1”,“reconnecttimeout”:""},{“id”:“879f4f95.50fa7”,“type”:“MSSQL-CN”,“z”:"",“tdsVersion”:“7_4”,“name”:“shu”,“server”:“DESKTOP-KSETL7L”,“port”:“1433”,“encyption”:true,“database”:“shubham”,“useUTC”:true,“connectTimeout”:“15000”,“requestTimeout”:“15000”,“cancelTimeout”:“5000”,“pool”:“5”},{“id”:“8602445f.047c78”,“type”:“inject”,“z”:“1d060b.2739c9f6”,“name”:"",“topic”:"",“payload”:"",“payloadType”:“date”,“repeat”:"",“crontab”:"",“once”:false,“onceDelay”:0.1,“x”:180,“y”:140,“wires”:[[“a3059bfe.304028”]]},{“id”:“bee9e263.ae405”,“type”:“debug”,“z”:“1d060b.2739c9f6”,“name”:"",“active”:false,“tosidebar”:true,“console”:false,“tostatus”:false,“complete”:“false”,“x”:810,“y”:140,“wires”:[]},{“id”:“a3059bfe.304028”,“type”:“modbustcp-read”,“z”:“1d060b.2739c9f6”,“name”:“shubham”,“topic”:“modbus”,“dataType”:“HoldingRegister”,“adr”:“1”,“quantity”:“2”,“rate”:“3”,“rateUnit”:“ms”,“server”:“380a7e52.021592”,“ieeeType”:“off”,“ieeeBE”:true,“x”:410,“y”:140,“wires”:[[“2c6a3d39.1c6b02”]]},{“id”:“2c6a3d39.1c6b02”,“type”:“MSSQL”,“z”:“1d060b.2739c9f6”,“mssqlCN”:“879f4f95.50fa7”,“name”:“mssql”,“query”:"",“outField”:“payload”,“x”:590,“y”:140,“wires”:[[“bee9e263.ae405”]]}]

You can not go from the Modbus node straight into the SQL node. There must be a function block (at least) between to the two.
Ok, first things first. Have you proven that the Modbus node is working by deleting the MSSQL node and putting the debug on the modbus node?
That’s the first thing you need to do. Take it step by step, node by node. Start with the Modbus and make sure you are getting data out of that node and see what format the data is in before we go any further.

Also, when posting flows to the forums you MUST put them in ‘preformatted text’ other wise we can not use / test them.

sir i am getting modbus data perfectly.as shwon below:-

Great. Thats a good start. Can you pleast click on the little arrow in the debug tab and break down the msg.payload so we can see what the two parameters are.
So we know the Modbus is working, now to get the data into MYSQL.
Lets start with in an inject node and a mysql node. Nothing else, just those to nodes and prove that we are getting data into mysql.

What do you use to check your mysql data? phpmyadmin or something else?

sir actually i am using mssql…
flow3

is it fine with mssql or i have to use mysql…

Sorry, my bad, I am more comfortable with MySQL than MSSQL.
It is fine to use MSSQL.
Please take some time to read through these threads; https://forums.opto22.com/search?q=mssql
They should give you enough help to get going.