UnDP poller / SWQL query for custom temperature dashboard with HTML tags

Good morning all,

I had a request from a customer to create a dashboard for AVTech RoomAlert 12S temperature probes, with color coded temperature values and a hyperlink to the device's node details page.  I did not find anything OOTB for this, so here is my process:

1. UnDP- I created custom pollers for the unit temperature, port1, and external probe ports.  AVTech's documentation was completely wrong and it took me some time to figure out what is what and pin down the values I actually needed to be working with (which OID is actually a temperature value???)

2. UnDP- AVTech returns temperature values in 4 digits, so 72.72 degrees comes back as "7272".  I created a data transform for each value.  For the External/Port 1 temp, this transform was simply {Custom_Poller_Name}/100.

For the unit temperature, the only value I was able to get to work was Celsius.  Since I am based in the US, I needed to convert this to Freedom units, so my conversion on this is CtoF({AVTech_Unit_Custom_Poller}/100)

3. Database Manager/SSMS/etc- The table we are looking at is CustomPollerAssignment.  If you query this table, you can get a couple of needed values: ID, and NodeID.

Use the NodeID to compare to the node ID of your device in particular.  You'll want to make sure you are viewing the entry ID of your UnDP transform, if needed, or your Custom Poller, if not.  For instance, I am looking at node ID 555.  I look at the CustomPollerAssignment table and see that node ID 555 has three ID's assigned to it: two are pollers, one is a data transform.  I need the transform, so I will look at the "Assignment Name" column, see my transform name, and see that the ID for this table is 1515.  This is the ID I will use for the following SWQL query.

4. Orion web- Wherever you want to display this, customize the page and add a Custom Table widget.  This seems to be the only widget type that supports HTML tags in the way I'm using them.

Edit the table.  Select "Edit Datasource" near the top, select "Advanced Database Query" as the selection method, check the SWQL radio button.

--Query Start

SELECT Top 4

CASE WHEN CurrentValue < '80' THEN concat('<a href="https://**orion-or-aws**/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:XXX" style="color:Chartreuse;font-size:300%;">',CurrentValue,'</a>')

WHEN CurrentValue < '90' and CurrentValue >= '80' THEN concat('<a href="https://**orion-or-aws**/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:XXX" style="color:Orange;font-size:300%;">',CurrentValue,'</a>')

WHEN CurrentValue >= '90' THEN concat('<a href="https://**orion-or-aws**/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:XXX" style="color:Crimson;font-size:300%;">',CurrentValue,'</a>')

end AS [CurrentValue], [CustomPollerDescription]

FROM [Orion.NPM.CustomPollerAssignment]

WHERE ID=1111 OR ID=1112 OR ID=1113 OR ID=1114

--Query end

SELECT TOP X -- how many values do you want to display?  In this case, I have 4 temperatures I want to show.

The URLs- copy/paste the node details URL to your device here

WHERE ID=XXXX -- This is the ID or IDs mentioned in step 3 above.

On Table Layout on the Edit Resource page after your query is complete, you will have to select the 2 fields returned.

Last thing you will need to do, is expand Advanced under the CurrentValue column, and check the box next to Allow HTML tags.  You may also want to rename the columns while you're here to something more user friendly.

Attachment is an example of how mine looks.  Any feedback appreciated.

Parents Reply Children
No Data