This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Duplicate "Node Tabular Universal Device Poller" Resource In Order To Add A Status Icon

jdumke over 7 years ago

I'd like to be able to replicate the information in this Node Tabular Universal Device Poller resource because my engineers want more visual notification if the EqualLogic disk status is on-line or not.

1) Could this be done with an Application Monitor? I don't see how, because when I set up the Application Monitor to monitor the EQLDSKSTATUS OID, I get a simple application up or down status.

2) Could this be done with a SWQL query? Not being a SQL person, I can't figure out how to pull these four OIDS from the Custom Poller table in a table format, and then add a status icon based on the EQLDISKSTATUS.

Top Replies

0 planglois over 7 years ago

I know you inquired about SWQL, but here's a start in SQL that could help you.

Using the following SQL query to define your data source in a "custom table" web resource, you could then build a similar table.

SELECT    
    A.nodeid,
    A.rowid,
    CAST(A.value AS INT) AS SNMP_TABLE_COLUMN1,
    CAST(B.value AS INT) AS SNMP_TABLE_COLUMN2,
    C.value AS SNMP_TABLE_COLUMN3,
    D.value AS SNMP_TABLE_COLUMN4,
FROM         
    (   SELECT 
            CPA.nodeid,
            CPA.custompollerassignmentid,
            CPS.[value],
            CPS.[rowid]
        FROM   [SolarWindsOrion].[dbo].[CustomPollerStatusTable] CPS
        INNER JOIN custompollerassignment CPA
            ON CPS.custompollerassignmentid = CPA.custompollerassignmentid
        INNER JOIN custompollers CP
            ON CP.custompollerid = CPA.custompollerid
        AND CP.uniquename = 'SNMP_TABLE_COLUMN1'
    ) A
INNER JOIN 
    (    SELECT 
            CPA.nodeid,
            CPA.custompollerassignmentid,
            CPS.[value],
            CPS.[rowid]
        FROM   [SolarWindsOrion].[dbo].[CustomPollerStatusTable] CPS
        INNER JOIN custompollerassignment CPA
            ON CPS.custompollerassignmentid = CPA.custompollerassignmentid
        INNER JOIN custompollers CP
            ON CP.custompollerid = CPA.custompollerid
        AND CP.uniquename = 'SNMP_TABLE_COLUMN2'
    ) B 
    ON A.nodeid = B.nodeid
    AND A.rowid = B.rowid
INNER JOIN 
    (    SELECT 
            CPA.nodeid,
            CPA.custompollerassignmentid,
            CPS.[value],
            CPS.[rowid]
        FROM   [SolarWindsOrion].[dbo].[CustomPollerStatusTable] CPS
        INNER JOIN custompollerassignment CPA
            ON CPS.custompollerassignmentid = CPA.custompollerassignmentid
        INNER JOIN custompollers CP
            ON CP.custompollerid = CPA.custompollerid
        AND CP.uniquename = 'SNMP_TABLE_COLUMN3'
    ) C
    ON A.nodeid = C.nodeid        
    AND A.rowid = C.rowid
INNER JOIN 
    (    SELECT 
            CPA.nodeid,
            CPA.custompollerassignmentid,
            CPS.[value],
            CPS.[rowid]
        FROM   [SolarWindsOrion].[dbo].[CustomPollerStatusTable] CPS
        INNER JOIN custompollerassignment CPA
            ON CPS.custompollerassignmentid = CPA.custompollerassignmentid
        INNER JOIN custompollers CP
            ON CP.custompollerid = CPA.custompollerid
        AND CP.uniquename = 'SNMP_TABLE_COLUMN3'
    ) D
    ON A.nodeid = D.nodeid        
    AND A.rowid = D.rowid
WHERE A.nodeid =  ${NodeID}
ORDER BY len(b.value), b.value, len(a.value), a.value ASC

replace 'SNMP_TABLE_COLUMNx' by your unique poller name (as defined in UnDP), and it should work.

Once tested, you could massage it to generate the icon that could be used when displayed. (ie: if on-line = green, if offline=red) with the CASE operator in your select statement (before the first "FROM"):

CASE WHEN C.value = 'on-line' THEN 'up.gif'

WHEN C.Value = 'offline' THEN 'down.gif'

ELSE 'unknown.gif'

END as ICON_COLUMN

make sure you place your icon path (in the advanced configuration of your column) to "/Orion/Images/StatusIcons/{0}"

Hopefully, you'll be able to do some mileage on this

0 mesverrum over 7 years ago in reply to planglois

This is a solid SQL solution to this problem, I just wanted to add in an example of doing it with SWQL, this is an older query I had used in the past before NPM 12 redid their native F5 integration. Demonstrates pulling data from several UNDP pollers, icons, links, etc. Along with that I also had some other queries that demo the case logic for presentation purposes

select s.status as [F5 Node],t4.status as [Port], t2.status as [Pool Name], t3.status as [Enabled], t5.status as [Monitor Status], n.caption as [F5], n.detailsurl as [_linkfor_F5]
,'/orion/images/statusicons/small-'+ n.statusled as [_iconfor_F5],
case when t5.status like 'unchecked' then '/orion/images/statusicons/small-unknown.gif' when t3.status !='enabled' then '/orion/images/statusicons/small-down.gif' when t5.status !='up' then '/orion/images/statusicons/small-down.gif' else '/orion/images/statusicons/small-up.gif'
end as [_iconfor_F5 Node]
from Orion.NPM.CustomPollerStatus s
join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID
join Orion.F5.Device d on d.nodeid=a.nodeid
join orion.nodes n on n.nodeid=d.nodeid
join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID
from Orion.NPM.CustomPollerStatus s
join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID
join Orion.F5.Device d on d.nodeid=a.nodeid
where a.CustomPollerName like 'F5ltmPoolMemberPoolName'
and minutediff(s.datetime,getutcdate())<6
and d.FailoverState=4) as t2 on s.rowid=t2.rowid
join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID
from Orion.NPM.CustomPollerStatus s
join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID
join Orion.F5.Device d on d.nodeid=a.nodeid
where a.CustomPollerName like 'F5ltmPoolMemberSessionStatus'
and minutediff(s.datetime,getutcdate())<6
and d.FailoverState=4) as t3 on s.rowid=t3.rowid
join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID
from Orion.NPM.CustomPollerStatus s
join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID
join Orion.F5.Device d on d.nodeid=a.nodeid
where a.CustomPollerName like 'F5ltmPoolMemberPort'
and minutediff(s.datetime,getutcdate())<6
and d.FailoverState=4) as t4 on s.rowid=t4.rowid
join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID
from Orion.NPM.CustomPollerStatus s
join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID
join Orion.F5.Device d on d.nodeid=a.nodeid
where a.CustomPollerName like 'F5ltmPoolMemberMonitorStatus'
and minutediff(s.datetime,getutcdate())<6
and d.FailoverState=4) as t5 on s.rowid=t5.rowid
where a.CustomPollerName like 'F5ltmPoolMemberNodeName'
and minutediff(s.datetime,getutcdate())<6
and d.failoverstate=4
and (s.status like '%${SEARCH_STRING}%' or t2.status like '%${SEARCH_STRING}%' or t3.status like '%${SEARCH_STRING}%')

This one has case logic for icons and such

SELECT p.Name
,case
when p.mode=17 then 'Ratio Session'
when p.mode=16 then 'Weighted Least Conn Node Addr'
when p.mode=15 then 'Weighted Least Conn Member'
when p.mode=14 then 'L3 Address'
when p.mode=13 then 'Dynamic Ratio Member'
when p.mode=12 then 'Least Sessions'
when p.mode=11 then 'Fastest App Response'
when p.mode=10 then 'Dynamic Ratio'
when p.mode=9 then 'Predictive Node Address'
when p.mode=8 then 'Observed Node Address'
when p.mode=7 then 'Fastest Node Address'
when p.mode=6 then 'Least Conn Node Address'
when p.mode=5 then 'Ratio Node Address'
when p.mode=4 then 'Predictive Member'
when p.mode=3 then 'Observed Member'
when p.mode=2 then 'Least Conn Member'
when p.mode=1 then 'Ratio Member'
when p.mode=0 then 'Round Robin'
end as [Mode]
,case
when p.EnabledState=3 then 'Disabled by Parent'
when p.EnabledState=2 then 'Disabled'
when p.EnabledState=1 then 'Enabled'
when p.EnabledState=0 then 'None'
end as [Enabled]
, case
when p.AvailabilityState=4 then 'Unknown'
when p.AvailabilityState=3 then 'Offline'
when p.AvailabilityState=2 then 'Disabled'
when p.AvailabilityState=1 then 'Available'
end as [Availability]
,case
when p.Status=1 then '/orion/images/statusicons/small-up.gif'
when p.Status=2 then '/orion/images/statusicons/small-shutdown.gif'
when p.Status=3 then '/orion/images/statusicons/small-down.gif'
when p.Status=4 then '/orion/images/statusicons/small-unknown.gif'
end as [_iconfor_Name]
, n.caption as F5
, n.detailsurl as [_linkfor_F5]
,'/orion/images/statusicons/small-'+ n.statusled as [_iconfor_F5]
FROM Orion.F5.Pools p
join orion.nodes n on p.nodeid=n.nodeid
join orion.f5.device d on d.nodeid=p.nodeid
where
(p.name like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems