9 Replies Latest reply on Oct 7, 2015 12:59 PM by bmerrill@ameritas.com

    UnDP Table Count?

    bmerrill@ameritas.com

      I'm very new to NPM.  What I'm trying to create is an alert that will let me know if my VPN tunnel is not complete.  I have created a UnDP that is giving me the raw data to work with it looks like this:

       

       

      What I want to do now is count how many rows have the data I'm looking for, for example 4237.21D2 would count 5 in my example above, then have SolarWinds alert me if that count is ever less than 5.  Is there a way to accomplish this?

       

      Thanks!

        • Re: UnDP Table Count?
          zackm

          This is absolutely something that can be accomplished, but it will require some customization in the alert. I would recommend posting this over in the Alert Lab.

           

          Basically, you're going to need a custom SQL alert that will count the # of times a specific value is provided from the dbo.CustomPollerStatusTable view. It's not that hard, but if you're not comfortable with SQL, you might need a little help.

          • Re: UnDP Table Count?
            bmerrill@ameritas.com

            My earlier post was in Alert Manager on the server, which it looks like to me is not the right place to try this?  This is my attempt using the Web Interface.  I tried adding HAVING to this but keep getting query condition is invalid if I try that.

             

              • Re: UnDP Table Count?
                zackm

                you need to do something like this:

                 

                select stuff
                from tableA

                -----------------

                join

                (select count(1), b.key

                from tableB

                where b.column = "string"

                group by b.key

                having count(1) > 5

                ) x on x.key = tableA.key

                1 of 1 people found this helpful
                  • Re: UnDP Table Count?
                    bmerrill@ameritas.com

                    Getting closer, zackm.  Your help has been awesome so far.

                    Here's the query you gave me inside the Join above.  Works perfectly in Orion Database Manager when not inside a Join.  Returns a row when the value matches the count I am expecting, 20:

                     

                    Next, here is the Join when setting up the custom alert.  Works good to this point:

                     

                     

                    However, as soon as I add the Count function, the Validation fails:

                     

                     

                    Since the syntax checks out in the Database Manager, is there something missing in the SQL alert configuration that doesn't allow the Count function?

                      • Re: UnDP Table Count?
                        sean.martinez

                        I attempted your query and it did work, but the moment I added the Pre-SQL that the Alert includes, it does fail because count needs to be tied to a column to count.

                         

                        So I looked into your alert and I figured out is that the Current Alerting system you are supposed to Select Custom Table Poller Current Status. This SWQL Table has the column status that you need to filter, however the dropdown does not have Status as a selectable field, but it can be done. The Alerts have the hamburger menu option to show the SWQL Queries when I was limited in the Alert Dropdowns. Then I used SWQL Studio to find all of the information.

                         

                        alert.png

                         

                        All you need to do is change I want to ALert on to the bottom to select Custom SWQL, then use Custom Table Poller Current Status. I then added Status= the result.

                         

                         

                        This is the full SWQL View that you can leverage:

                         

                        SELECT CustomPollerAssignmentID, CompressedRowID, RowLabel, AssignmentName, CustomPollerID, NodeID, DateTime, Rate, Total, RawStatus, Status, Description, RowStatus, RowID, DetailsUrl, DisplayName, InstanceType, Uri

                        FROM Orion.NPM.CustomPollerStatusOnNodeTabular

                         

                        As I mentioned the Drop down only has a few options: Current Numeric Value, Current Value, Date Time, Display Name, Rate, Row ID, Row Label, Row Status, Total.

                         

                        I needed to setup an Alert due to one of my RAID arrays falling into a Degraded state. So I have Custom Node Poller to pull this information.

                         

                        I saved my Alert as above and I just got an E-Mail that my FreeNAS RAID Array is now degraded.

                      • Re: UnDP Table Count?
                        bmerrill@ameritas.com

                        The only thing I needed to add to the answer marked as correct was a column name for count.  Some SQL query tools will work without that but not SolarWinds Custom SQL Poller so below is what worked:

                         

                        select stuff
                        from tableA

                        -----------------

                        join

                        (select count(1) as ColumnName, b.key

                        from tableB

                        where b.column = "string"

                        group by b.key

                        having count(1) > 5

                        ) x on x.key = tableA.key