Custom property values will be null if nothing has ever been entered for that property on a given node. If something was entered and later deleted, they will be empty ('').
To catch both cases, you can use the SQL isnull function. If your property called is Notes, then this query should do it:
SELECT * FROM Nodes WHERE ISNULL(Notes, '') = ''
I would like to do the same thing but instead delete all rows that have a null value for a certain Custom Poller from the Universal device poller program, could you provide me with that SQL code?
Custom pollers are more complex. Could you give me a more detailed description of what you are trying to do? Are you trying to remove nodes? Or just custom poller assignments?
We have a custom poller (OID: 18.104.22.168.22.214.171.124.126.96.36.199) that Polls every node in our network for serial numbers. However, every piece of hardware may not have a serial number. For example, in the following output, I only want to display the information for the 2x 10Gbase-ER GBIC's. I want to delete all rows in the table where the OID above is blank. In addition, I would like to put together a report that showed me all Serial Numbers for all nodes in the network.
10/100/1000BaseT Gi1/7 10/100/1000BaseT Gi1/9 10/100/1000BaseT Gi1/8 10Gbase-ER Te4/1 OPP10410057 10Gbase-SR Te4/2 PLC1037H03U 10-Gigabit Port Container 10-Gigabit Port Container 4/4 10-Gigabit Port Container 10-Gigabit Port Container 4/3
So, can you help me write a report that shows me all serial numbers in the network with their interfaces and media type for all nodes in our network? Also, my custom poller is no longer polling for serial numbers and I would like to just delete all rows in the "CustomPollerStatistics_Detail" where the column "Status" is empty. Can you help out with my requests?
The Universal Device Poller doesn't have a way to specify which rows of the table to keep. You could write a query that would delete the rows with no values, but they would just come back in the next polling cycle.
Here's a query that will get you started on building that report:
select CustomPollerAssignment.NodeID, CustomPollerLabels.Label, CustomPollerStatus.Status, Interfaces.InterfaceID from CustomPollerStatus
inner join CustomPollerAssignment on CustomPollerAssignment.CustomPollerAssignmentID=CustomPollerStatus.CustomPollerAssignmentID
inner join CustomPollers on CustomPollerAssignment.CustomPollerID=CustomPollers.CustomPollerID
inner join CustomPollerLabels on CustomPollerLabels.CustomPollerAssignmentID=CustomPollerAssignment.CustomPollerAssignmentID and CustomPollerLabels.RowID=CustomPollerStatus.RowID
left outer join Interfaces on CustomPollerLabels.Label like Interfaces.InterfaceName and CustomPollerAssignment.NodeID=Interfaces.NodeID
where CustomPollers.UniqueName='entPhysicalSerialNum' and CustomPollerStatus.Status<>''
I set up my entPhysicalSerialNum poller to use entPhysicalName for its labels. However, there is apparently only one interface on one node in our lab (an old Cisco 1200 WAP) where the entPhysicalName matches the InterfaceName. Maybe you'll have better luck with your devices.
For some reason the resource I was using did not like the whole sytax. I had to modify your query into:
ISNULL(Notes, '') Like ''
Thanks for your help.