Showing query results in Dashboard using </Template> node

344 views
Skip to first unread message

Mike Arney

unread,
Jan 22, 2018, 3:01:21 PM1/22/18
to Node-RED
I am trying to show query results on in a dashboard group. I can get the last one to show, but it will now show them all(only have 2 right now). Here is what I have:

[{"id":"832c4b9c.3edb28","type":"function","z":"bd6bddc.e9ef72","name":"SQL","func":"pld = \"SELECT Reason, Operator, Timedown \";\npld = pld + \"FROM DownTime \";\npld = pld + \"WHERE Timedown >= DATEADD(Day, -1, sysutcdatetime()) \";\npld = pld + \"ORDER BY Timedown \";\n\nmsg.payload = pld;\n\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":980,"wires":[["7dba7003.281c3"]]},{"id":"7dba7003.281c3","type":"MSSQL","z":"bd6bddc.e9ef72","mssqlCN":"4f854204.cc081c","name":"MSSQL","query":"","outField":"payload","x":520,"y":980,"wires":[["d03b4912.6cde18"]]},{"id":"893227a3.90d408","type":"inject","z":"bd6bddc.e9ef72","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":160,"y":980,"wires":[["832c4b9c.3edb28"]]},{"id":"eaab3407.a38dc8","type":"function","z":"bd6bddc.e9ef72","name":"","func":"var r = msg.payload.Reason;\nvar o = msg.payload.Operator;\nvar t = msg.payload.Timedown;\n\nmsg.payload = \"Reason: '\"+r+\"' Operator: '\"+o+\"' TimeDown: '\"+t+\"'\";\n\nreturn msg;","outputs":1,"noerr":0,"x":810,"y":980,"wires":[["35bfd128.42c09e"]]},{"id":"d03b4912.6cde18","type":"split","z":"bd6bddc.e9ef72","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":980,"wires":[["eaab3407.a38dc8"]]},{"id":"35bfd128.42c09e","type":"ui_template","z":"bd6bddc.e9ef72","group":"93fdb112.ec249","name":"Down Time Instances","order":0,"width":"5","height":"8","format":"\n<head><h4>Down Time Instances</h4></head>\n<br>\n<br>\n<div class=\"payload1\">{{msg.payload}}</div>\n\n","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":1060,"y":980,"wires":[[]]},{"id":"4f854204.cc081c","type":"MSSQL-CN","z":"","name":"Dev","server":"10.0.0.120","encyption":true,"database":"Dev"},{"id":"93fdb112.ec249","type":"ui_group","z":"","name":"Machine State","tab":"4a3f0164.a412d","order":1,"disp":true,"width":"5"},{"id":"4a3f0164.a412d","type":"ui_tab","z":"","name":"HT Mobile","icon":"dashboard","order":1}]

Mike Arney

unread,
Jan 23, 2018, 9:41:44 AM1/23/18
to Node-RED
I'm getting closer, but could use a little help.

 
 









[{"id":"832c4b9c.3edb28","type":"function","z":"bd6bddc.e9ef72","name":"SQL","func":"pld = \"SELECT Reason, Operator, Timedown \";\npld = pld + \"FROM DownTime \";\npld = pld + \"WHERE Timedown >= DATEADD(Day, -1, sysutcdatetime()) \";\npld = pld + \"ORDER BY Timedown \";\n\nmsg.payload = pld;\n\nreturn msg;","outputs":1,"noerr":0,"x":230,"y":980,"wires":[["7dba7003.281c3"]]},{"id":"7dba7003.281c3","type":"MSSQL","z":"bd6bddc.e9ef72","mssqlCN":"4f854204.cc081c","name":"MSSQL","query":"","outField":"payload","x":360,"y":980,"wires":[["d03b4912.6cde18"]]},{"id":"893227a3.90d408","type":"inject","z":"bd6bddc.e9ef72","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":100,"y":980,"wires":[["832c4b9c.3edb28"]]},{"id":"eaab3407.a38dc8","type":"function","z":"bd6bddc.e9ef72","name":"","func":"var r = msg.payload.Reason;\nvar o = msg.payload.Operator;\nvar t = msg.payload.Timedown;\nvar arr = new Array([r, o, t]);\n\nmsg.payload = \"Down time instance: '\"+arr+\"' ---- \";\n//msg.payload = \"Reason: '\"+r+\"' Operator: '\"+o+\"' TimeDown: '\"+t+\"'\";\n\nreturn msg;","outputs":1,"noerr":0,"x":690,"y":980,"wires":[["fc0dc0c2.db40d"]]},{"id":"d03b4912.6cde18","type":"split","z":"bd6bddc.e9ef72","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":510,"y":1100,"wires":[["eaab3407.a38dc8"]]},{"id":"35bfd128.42c09e","type":"ui_template","z":"bd6bddc.e9ef72","group":"93fdb112.ec249","name":"Down Time Instances","order":0,"width":"5","height":"8","format":"\n<head><h4>Down Time Instances</h4></head>\n<br>\n<br>\n<div class=\"payload1\">{{msg.payload}}</div>\n\n","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":1020,"y":980,"wires":[[]]},{"id":"7c808b44.6f1014","type":"debug","z":"bd6bddc.e9ef72","name":"","active":true,"console":"false","complete":"false","x":930,"y":1100,"wires":[]},{"id":"fc0dc0c2.db40d","type":"join","z":"bd6bddc.e9ef72","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","x":740,"y":1100,"wires":[["7c808b44.6f1014","35bfd128.42c09e"]]},{"id":"4f854204.cc081c","type":"MSSQL-CN","z":"","name":"Dev","server":"10.0.0.120","encyption":true,"database":"Dev"},{"id":"93fdb112.ec249","type":"ui_group","z":"","name":"Machine State","tab":"4a3f0164.a412d","order":1,"disp":true,"width":"5"},{"id":"4a3f0164.a412d","type":"ui_tab","z":"","name":"HT Mobile","icon":"dashboard","order":1}]

steve rickus

unread,
Jan 23, 2018, 4:17:11 PM1/23/18
to Node-RED
Hi Mike,

It looks like you have got yourself a bit stuck in the weeds, with all the result splitting/formatting/joining and such...
From the looks of it, you just need a way to query the database for some rows, and display the results in html tabular form -- is that about right?

The way I would approach this is to keep your results intact, as an array of objects, until it's time to render them in the browser. It would help if I could see your raw query results, but from your function code, I can see you are extracting the Reason, Operator, and Timedown values from each row. Rather than splitting the array into rows, and running each through a function to reformat it as a string, you can:
  • use either a regular template node (iterate through the input array, using mustache substitution to output text strings),
    {{#payload}}
    Downtime instance: {{Reason}}, {{Operator}}, {{Timedown}}
    {{/payload}}

    which are placed inside the <div> within this ui_template node (using styling to preserve line breaks and spacing),
    <style>
        #mylog {
            min-height: 300px;
            padding: 0;
            white-space: pre;
        }
    </style>

    <div id="mylog" ng-bind="msg.payload" contenteditable="true">
        <!-- query results will go here -->
    </div>

  • or pass the result array directly to the ui_template and use angular syntax to iterate over the rows, building a table like so
    <style>
        th {
            text-decoration: underline;
        }
        tbody tr:hover {
            color: darkorange;
            cursor: pointer;
        }
    </style>

    <table style="width: 100%;">
        <thead>
            <tr>
                <th>Reason</th>
                <th>Owner</th>
                <th>Time Down</th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="row in msg.payload">
                <td>{{row.Reason}}</td>
                <td>{{row.Owner}}</td>
                <td>{{row.Timedown | date}}</td>
            </tr>
        </tbody>
    </table>

There is no "correct" way -- it mostly depends upon which syntax is most familiar, and how you want the dashboard content to look. Angular has some nice formatting directives (like the date above), and if/then/else logic -- whereas mustache syntax is simpler but without those features.
--
Steve

Mike Arney

unread,
Jan 24, 2018, 1:29:14 PM1/24/18
to Node-RED
Thank you Steve!

I had to remove the " |  date" portion of {{row.Timedown | date}} because I need it to be a local timestamp. The only problem that I have, now, is that the time is not showing as the local time.
It is showing as sysutctime, as it is saved in my database. Here is a picture of my results:



I need the Timedown to show like this:




Is there something else that I can put behind the  {{row.Timedown | date}}?


Thanks,

Mike








Dave C-J

unread,
Jan 24, 2018, 1:45:44 PM1/24/18
to node...@googlegroups.com

Mike Arney

unread,
Jan 24, 2018, 2:02:25 PM1/24/18
to Node-RED
Thank you Dave!
I will bookmark this page. I really do try to do it on my own first, but I do not have any experience yet. I am taking some classes on Javascript, NodeJS, and Angular.

On Wednesday, January 24, 2018 at 1:45:44 PM UTC-5, Dave C-J wrote:
Reply all
Reply to author
Forward
0 new messages