Is this OK for you?
Yes.
Try this...
[{"id":"f95b91f8.8471e","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":540,"y":1400,"wires":[["c1f4ea1.a942e18"]]},{"id":"c1f4ea1.a942e18","type":"http request","z":"a07b3581.696e08","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://172.17.0.115/api/current/kpis/latest?asset-id=6fa8b258-0713-4fdb-9965-f4beec7b56e4&type=POWER_CAPACITY","tls":"","persist":false,"proxy":"","authType":"basic","x":710,"y":1400,"wires":[["345e5ec.ff3a3a2"]]},{"id":"345e5ec.ff3a3a2","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":890,"y":1400,"wires":[]},{"id":"aecdaa58.9027f8","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":200,"y":100,"wires":[["1e22e438.94c14c"]]},{"id":"ab363ea7.17d48","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Alarms","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/alarms","tls":"","persist":false,"proxy":"","authType":"bearer","x":570,"y":100,"wires":[["7726a377.1f746c"]]},{"id":"1e22e438.94c14c","type":"function","z":"a07b3581.696e08","name":"Auth Key","func":"msg.headers = { \n \"Content-Type\": \"application/json\"\n };\n\n\nmsg.payload = {auth_key:\"069e2b9c-682d-4780-83c2-d87fe07e87dc\"};\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":100,"wires":[["ab363ea7.17d48"]]},{"id":"d24f6cce.e58a3","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":990,"y":180,"wires":[]},{"id":"7726a377.1f746c","type":"function","z":"a07b3581.696e08","name":"Filter","func":"\n\nmsg.payload = msg.payload.alarms.filter(function( obj ) {\n return !obj.clearedTime;\n});\nreturn msg;","outputs":1,"noerr":0,"x":770,"y":100,"wires":[["91a36c6a.043f8","d24f6cce.e58a3","9e54d0d8.2acf9"]]},{"id":"91a36c6a.043f8","type":"function","z":"a07b3581.696e08","name":"Count Active Alarms","func":"var newMsg = { payload: msg.payload.alarms.length };\nreturn [newMsg, msg];","outputs":1,"noerr":0,"x":1020,"y":100,"wires":[[]]},{"id":"50d7b36d.53466c","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Inventory","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/inventory","tls":"","persist":false,"proxy":"","authType":"bearer","x":560,"y":140,"wires":[["d24f6cce.e58a3"]]},{"id":"7fba9d7e.1c8b04","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Sensors","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/sensors","tls":"","persist":false,"proxy":"","authType":"bearer","x":570,"y":180,"wires":[["d24f6cce.e58a3"]]},{"id":"cc3ee9c6.db6208","type":"MSSQL","z":"a07b3581.696e08","mssqlCN":"a51e405c.10f64","name":"SQL Server","query":"INSERT INTO [MyTable] (id, name, quantity) VALUES ({{{payload.id}}}, '{{{payload.name}}}', {{{payload.quantity}}})\n","outField":"payload","returnType":"0","throwErrors":1,"x":710,"y":1300,"wires":[["6f056873.aeef98"]]},{"id":"9ab55052.c844e","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"{\"id\": \"110\", \"name\": \"node-red-Maxine\", \"quantity\":\"1972\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":530,"y":1300,"wires":[["cc3ee9c6.db6208"]]},{"id":"6f056873.aeef98","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":890,"y":1300,"wires":[]},{"id":"9e54d0d8.2acf9","type":"split","z":"a07b3581.696e08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":510,"y":300,"wires":[["9058bf96.e51c9","c536f869.093988"]]},{"id":"c536f869.093988","type":"MSSQL","z":"a07b3581.696e08","mssqlCN":"a51e405c.10f64","name":"SQL Server","query":"INSERT INTO \n [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) \nVALUES \n ('{{{payload.id}}}', '{{{payload.deviceId}}}', '{{{payload.label}}}', '{{{payload.message}}}', '{{{payload.serverity}}}', '{{{payload.activatedTime}}}', '{{{payload.clearedTime}}}')\n","outField":"payload","returnType":"1","throwErrors":1,"x":650,"y":380,"wires":[["e0cc2c9d.3f42b"]]},{"id":"9058bf96.e51c9","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":990,"y":300,"wires":[]},{"id":"e0cc2c9d.3f42b","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":970,"y":380,"wires":[]},{"id":"e41ba7c5.c117f8","type":"inject","z":"a07b3581.696e08","name":"fake the API","topic":"","payload":"[{\"id\":\"49cd2d85-0e12-4147-8bfa-444eb43d7a4e\",\"deviceId\":\"a03394b0-9a49-45c7-afb4-07541a8aba47\",\"label\":\"Handle Alarmed\",\"message\":\"Rack Door Handle alarmed for sensor Back Door at NBRK0750 with state \",\"severity\":\"CRITICAL\",\"activatedTime\":\"2020-06-19T10:38:24.199Z\"},{\"id\":\"9dadad16-38fe-4ee8-9f65-ada755a97e10\",\"deviceId\":\"acb8c96e-db30-4b72-bd06-ab8706b43efd\",\"label\":\"Local Authorization Alarm\",\"message\":\"Local Authorization alarm for Back Door at NBRK0750\",\"severity\":\"INFO\",\"activatedTime\":\"2020-04-29T14:28:57.594Z\"}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":160,"wires":[["9e54d0d8.2acf9"]]},{"id":"6e8900c8.83277","type":"catch","z":"a07b3581.696e08","name":"","scope":["c536f869.093988"],"uncaught":false,"x":650,"y":460,"wires":[["416168d0.936108"]]},{"id":"416168d0.936108","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":970,"y":460,"wires":[]},{"id":"a51e405c.10f64","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"Local SQL","server":"WIN-DGQMGP7MTKJ","port":"1433","encyption":false,"database":"db1","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false}]
you also have a typo in the MSSQL node
change payload.serverity to payload.severity
Hi Steve-Mcl, thank you...
I actually had to put 2 x Split nodes to get it to work, great help and support - thank you!
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
