6 Replies Latest reply on Nov 17, 2008 8:35 AM by robertjuric

    Filter Blank Custom Properties

    robertjuric
      I'm trying to view all nodes with a blank entry for a custom property. I've tried: Notes Not Like ' ' but that does not return any results. How should I phrase my filter so I can select the nodes who have no notes entered in their custom properties?
        • Re: Filter Blank Custom Properties
          tdanner

          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, '') = ''

            • Re: Filter Blank Custom Properties

                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?

                • Re: Filter Blank Custom Properties
                  tdanner

                  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?

                    • Re: Filter Blank Custom Properties

                       We have a custom poller (OID: 1.3.6.1.2.1.47.1.1.1.1.11) 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/1000BaseTGi1/7 
                      10/100/1000BaseTGi1/9 
                      10/100/1000BaseTGi1/8 
                      10Gbase-ERTe4/1OPP10410057
                      10Gbase-SRTe4/2PLC1037H03U
                      10-Gigabit Port Container10-Gigabit Port Container 4/4 
                      10-Gigabit Port Container10-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?

                        • Re: Filter Blank Custom Properties
                          tdanner

                          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.

                    • Re: Filter Blank Custom Properties
                      robertjuric
                      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.