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.
EDIT 22 SEP 2022-
I ended up switching from SWQL to SQL for this script. I found that any time the temperature went over 100 (and sadly it has on multiple occasions, Southeast US and poor facilities), the temperatures went back to green. The only way to stop this from happening was to remove the '' that was causing the greater than/less than to freak out on 3 digit temperatures.
I also added a ROUND statement to remove the decimal places, as I felt they weren't necessary. As this page is monitored on screen on the operations floor, removing the decimals allowed me to increase font size. I also changed to a darker green that is more visible from across the room/more color blindness friendly.
SELECT Top 4
CASE WHEN ROUND(CurrentValue, 0) < 80 THEN concat('<a href="">--URL--/.../NodeDetails.aspx style="color:DarkGreen;font-size:400%;">',ROUND(CurrentValue, 0),'</a>')
WHEN ROUND(CurrentValue, 0) < 90 and ROUND(CurrentValue, 0) >= 80 THEN concat('<a href="">--URL--/.../NodeDetails.aspx style="color:Orange;font-size:400%;">',ROUND(CurrentValue, 0),'</a>')
WHEN ROUND(CurrentValue, 0) >= 90 THEN concat('<a href="">--URL--/.../NodeDetails.aspx style="color:Crimson;font-size:400%;">',ROUND(CurrentValue, 0),'</a>')
end AS CurrentValue, CustomPollerDescription
FROM [SolarWindsOrion].[dbo].[CustomPollerAssignmentView]
WHERE ID=123 OR ID=456 OR ID=789 OR ID=321
Attachment is an example of how mine looks. (The above 80 temperature is green because this particular farm's thresholds were adjusted at request of customer) Any feedback appreciated.
