Here is what i'm trying to do:
In our organization we want to do some clean-up of switch ports which are Operationally down but administratively up, in addition to implementing the solarwinds unpluggable feature. To do this I need to identify ports which can be administratively shutdown an which can be identified as unpluggables. The trick is to not shutdown ports which have unpluggable items in them like desktop computers. So I went to Report writer and operated on the premise that I could use the ifLastChange poller and sysuptime pollers to get me a list of how long since the last change of an interface and filter this list by down interfaces. So the assumption is if an interface is down and the ifLastChange transformation is greater than 10 days then we will mark that port for shutdown. I have a transform which subtracts the ifLastChange from Sysuptime and divides by 840000. With all the pollers applied.
I need the report to list by interface the time in days since last change, interface description, machine type, IP address, and Node name.,
Now, I am having a very hard time getting the query to work and i'm at the end of my SQL knowledge, so i'm asking the community to help me out on this one. I am getting a blank IFOpStatus column. I'm also getting an error whenever I remove the TOP 1 from the query, saying the query returned multiple values.
SELECT
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
Nodes.MachineType AS Machine_Type,
IFDescLabel = ( SELECT TOP 1 CustomPollerLabels.Label
FROM Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers
WHERE
Nodes.NodeID = CustomPollerAssignment.NodeID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
AND CustomPollers.UniqueName = 'InterfaceUptime'
),
IFUptime = ( SELECT TOP 1 CustomPollerStatus.Status
FROM Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers
WHERE
Nodes.NodeID = CustomPollerAssignment.NodeID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
AND CustomPollers.UniqueName = 'InterfaceUptime' AND (Nodes.MachineType LIKE '%37xx%') AND
(CustomPollers.UniqueName = 'InterfaceUptime') AND
(CustomPollerStatus.Rate > 90) AND (CustomPollerStatus.Rate < 100)
),
IFOpStatus = ( SELECT TOP 1 CustomPollerStatus.Status
FROM Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers
WHERE
Nodes.NodeID = CustomPollerAssignment.NodeID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
AND CustomPollers.UniqueName = 'ifOperStatus'
)
FROM Nodes
WHERE Nodes.NodeID IN ( SELECT Nodes.NodeID
FROM CustomPollerAssignment CPA
INNER JOIN CustomPollers CP
ON CPA.CustomPollerID = CP.CustomPollerID
AND CP.UniqueName IN ('InterfaceUptime', 'ifOperStatus') )
