ControlLogix to MySQL

#1

The following flow is how we move our data from an Allen Bradley ControlLogix PLC to mySQL. Every time the tag for the PCCC node changes state, based off a counter in the PLC, we trigger a set of IgnitionReads. The data from the reads are parsed individually and strung together into an array of objects. They are then sorted into a consistent order (for troubleshooting purposes and clarity), parsed to their appropriate mySQL column names (again, for troubleshooting and clarity), and pushed to mySQL.

We are new to node-red and ignition, so anyone's input on a better way to make our flows and programs is welcome and encouraged to share your thoughts!

[{"id":"c932d11b.508f3","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/ElapsedShiftTime","name":"ElapsedShiftTime","x":510,"y":157,"wires":[["c89bd3ee.d222f"]]},{"id":"a4b5859a.abcc","type":"pccc in","z":"e2d07ac5.b0d02","endpoint":"","mode":"single","variable":"N76","diff":true,"name":"","x":247,"y":338,"wires":[["68486d6e.8f3aac"]]},{"id":"8b8a1c95.258198","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/FillPercent","name":"FillPercent","x":490,"y":190,"wires":[["e1b866f8.aa1758"]]},{"id":"19c9bdc.222fe42","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/AverageFeedSpeed","name":"AverageFeedSpeed","x":522,"y":222,"wires":[["2100870a.e9e288"]]},{"id":"2d1166f4.ddbee2","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/AverageTransferSpeed","name":"AverageTransferSpeed","x":532,"y":255,"wires":[["c3306738.144f68"]]},{"id":"36dff0ec.aefed","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/MaximumSpeed","name":"MaximumSpeed","x":502,"y":288,"wires":[["28276ccb.206ba4"]]},{"id":"81b76704.7903","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/TimeRunningReady","name":"TimeRunningReady","x":522,"y":320,"wires":[["f7fba382.8bd988"]]},{"id":"e143cd36.2e9638","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/TimeRunningEmpty","name":"TimeRunningEmpty","x":512,"y":354,"wires":[["6861c7d5.bf121"]]},{"id":"dd52dcc7.d9bce8","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/PieceCount","name":"PieceCount","x":495,"y":386,"wires":[["b3e8bbd9.c4dbe"]]},{"id":"982f1d67.53abc","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/VolIn","name":"VolIn","x":475,"y":417,"wires":[["36eeee85.459f6a"]]},{"id":"47cf29c4.4d8998","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/VolOut","name":"VolOut","x":475,"y":448,"wires":[["6c343e7c.cc7d18"]]},{"id":"5f48a717.5e4f7","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopOperatorFrequency","name":"StopOperatorFrequency","x":533,"y":478,"wires":[["f5788bda.b2bfd"]]},{"id":"77423292.fc9974","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopOperatorDuration","name":"StopOperatorDuration","x":521,"y":509,"wires":[["5d9a6bd4.5e7e64"]]},{"id":"aad4946e.092718","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopAlignmentFrequency","name":"StopAlignmentFrequency","x":528,"y":541,"wires":[["bbcf3fa6.997a4"]]},{"id":"fd89467e.176828","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopAlignmentDuration","name":"StopAlignmentDuration","x":527,"y":574,"wires":[["f973c1a7.e01138"]]},{"id":"86fccabb.610478","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopOverDimFrequency","name":"StopOverDimFrequency","x":526,"y":607,"wires":[["69c6f3ed.bd76f4"]]},{"id":"4960c9bf.719ea8","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/Domo/StopOverDimDuration","name":"StopOverDimDuration","x":515,"y":641,"wires":[["bdbe0df2.3c532"]]},{"id":"a3c5f14d.d433b8","type":"ignition-tag-read","z":"e2d07ac5.b0d02","server":"45e2d8ce.d2f238","value":"","valueType":"msg.payload","tagPath":"Edge Nodes/Groov/Morton/Mill/DomoTest/DomoIndex","name":"DomoIndex","x":490,"y":124,"wires":[["ae2c5d8c.e1c67"]]},{"id":"faca6252.c7f648","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return {\n mcId : msg.DomoIndex,\n elapsedShift : msg.ElapsedShiftTime,\n fp : msg.FillPercent,\n avgfeedspeed : msg.AverageFeedSpeed,\n avgtransferspeed : msg.AverageTransferSpeed,\n maxspeed : msg.MaximumSpeed,\n timerunning : msg.TimeRunningReady,\n timerunningempty : msg.TimeRunningEmpty,\n piececount : msg.PieceCount,\n rawvol : msg.VolIn,\n finishedvol : msg.VolOut,\n stopopfreq : msg.StopOperatorFrequency,\n stopopdur : msg.StopOperatorDuration,\n stopalignfreq : msg.StopAlignmentFrequency,\n stopalignndur : msg.StopAlignmentDuration,\n stopdimfreq : msg.StopOverDimFrequency,\n stopdimdur : msg.StopOverDimDuration,\n}","outputs":1,"noerr":0,"x":1049,"y":479,"wires":[["205a58a9.8c3f78"]]},{"id":"ae2c5d8c.e1c67","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n DomoIndex : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":739,"y":124,"wires":[["dc7c04f9.b09ec8"]]},{"id":"c89bd3ee.d222f","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n ElapsedShiftTime : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":157,"wires":[["dc7c04f9.b09ec8"]]},{"id":"e1b866f8.aa1758","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n FillPercent : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":190,"wires":[["dc7c04f9.b09ec8"]]},{"id":"2100870a.e9e288","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n AverageFeedSpeed : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":739,"y":222,"wires":[["dc7c04f9.b09ec8"]]},{"id":"c3306738.144f68","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n AverageTransferSpeed : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":739,"y":255,"wires":[["dc7c04f9.b09ec8"]]},{"id":"28276ccb.206ba4","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n MaximumSpeed : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":739,"y":288,"wires":[["dc7c04f9.b09ec8"]]},{"id":"f7fba382.8bd988","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n TimeRunningReady : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":739,"y":320,"wires":[["dc7c04f9.b09ec8"]]},{"id":"6861c7d5.bf121","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n TimeRunningEmpty : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":354,"wires":[["dc7c04f9.b09ec8"]]},{"id":"b3e8bbd9.c4dbe","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n PieceCount : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":386,"wires":[["dc7c04f9.b09ec8"]]},{"id":"36eeee85.459f6a","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n VolIn : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":737,"y":416,"wires":[["dc7c04f9.b09ec8"]]},{"id":"6c343e7c.cc7d18","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n VolOut : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":447,"wires":[["dc7c04f9.b09ec8"]]},{"id":"f5788bda.b2bfd","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopOperatorFrequency : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":738,"y":478,"wires":[["dc7c04f9.b09ec8"]]},{"id":"5d9a6bd4.5e7e64","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopOperatorDuration : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":737,"y":509,"wires":[["dc7c04f9.b09ec8"]]},{"id":"bbcf3fa6.997a4","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopAlignmentFrequency : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":737,"y":541,"wires":[["dc7c04f9.b09ec8"]]},{"id":"f973c1a7.e01138","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopAlignmentDuration : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":736,"y":574,"wires":[["dc7c04f9.b09ec8"]]},{"id":"69c6f3ed.bd76f4","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopOverDimFrequency : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":736,"y":607,"wires":[["dc7c04f9.b09ec8"]]},{"id":"bdbe0df2.3c532","type":"function","z":"e2d07ac5.b0d02","name":"Parse Data","func":"return{\n StopOverDimDuration : msg.payload.ignitionResult.value,\n\n}","outputs":1,"noerr":0,"x":735,"y":641,"wires":[["dc7c04f9.b09ec8"]]},{"id":"dc7c04f9.b09ec8","type":"join","z":"e2d07ac5.b0d02","name":"","mode":"custom","build":"array","property":"","propertyType":"full","key":"topic","joiner":"\n","joinerType":"str","accumulate":false,"timeout":"","count":"17","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1051,"y":386,"wires":[["4032b221.50c834"]]},{"id":"68486d6e.8f3aac","type":"delay","z":"e2d07ac5.b0d02","name":"","pauseType":"delay","timeout":"1000","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":284,"y":395,"wires":[["a3c5f14d.d433b8","c932d11b.508f3","8b8a1c95.258198","19c9bdc.222fe42","2d1166f4.ddbee2","36dff0ec.aefed","81b76704.7903","e143cd36.2e9638","dd52dcc7.d9bce8","982f1d67.53abc","47cf29c4.4d8998","5f48a717.5e4f7","77423292.fc9974","aad4946e.092718","fd89467e.176828","86fccabb.610478","4960c9bf.719ea8"]]},{"id":"4032b221.50c834","type":"function","z":"e2d07ac5.b0d02","name":"Array Sort","func":"payloadArray = msg.payload;\n\nvar resultObject = payloadArray.reduce(function(result, currentObject) {\n for(var key in currentObject) {\n if (currentObject.hasOwnProperty(key)) {\n result[key] = currentObject[key];\n }\n }\n \n return result;\n}, {});\n\n\nreturn resultObject;\n","outputs":1,"noerr":0,"x":1052,"y":430,"wires":[["faca6252.c7f648"]]},{"id":"205a58a9.8c3f78","type":"function","z":"e2d07ac5.b0d02","name":"mySQL Write","func":"msg.topic = "INSERT INTO plctest (mcId, elapsedShift, fp, avgfeedspeed,avgtransferspeed,maxspeed,timerunning,timerunningempty,piececount,rawvol,finishedvol,stopopfreq,stopopdur,stopaligndur,stopdimfreq,stopdimdur) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";\nmsg.payload = [msg.mcId,msg.elapsedShift,msg.fp,msg.avefeedspeed,msg.avetransferspeed,msg.maxspeed,msg.timerunning,msg.timerunningempty,msg.piececount,msg.rawvol,msg.finishedvol,msg.stopopfreq,msg.stopopdur,msg.stopaligndur,msg.stopdimfreq,msg.stopdimdur];\nreturn msg;","outputs":1,"noerr":0,"x":1048,"y":530,"wires":[["cfab0670.ce414"]]},{"id":"cfab0670.ce414","type":"mysql","z":"e2d07ac5.b0d02","mydb":"","name":"","x":1037,"y":586,"wires":[[]]},{"id":"45e2d8ce.d2f238","type":"ignition-server","z":"","hostname":"localhost","port":"8088","ssl":false,"defaultTagProvider":"edge","name":""}]

0 Likes