8 Replies Latest reply on Oct 14, 2015 8:39 AM by taylor.greg

    VNQM IPSLA Report

    taylor.greg

      I am looking to create a report that takes specific IPSLA operations and based on a 300ms "Critical" threshold counts how many times this operation exceeded that threshold in a given month. I will then figure out a percentage based on that number outside of our threshold. As a bonus if you can subtract the number of critical events from 8640 and then divide that number by 8640 and multiplied by 100. That should give me a straight percentage. See below example. If anyone has any ideas on how to accomplish this it would be greatly appreciated. Thank you.


      Site SLA - Critical count 120

       

      8640 - 120 = 8520

      8520/8640 = 0.9861

      0.9861 x 100 = 98.61%

        • Re: VNQM IPSLA Report
          zackm

          You're going to want to know your OperationID to target specific Operations, but this is a good starting point.

           

          Also, this query counts the total polls for your so you do not have to guess at how many happened in the last 30 days (to account for different polling intervals)

           

          SELECT
            o.OperationName
            ,o.OperationType
            ,COUNT(r.VoipOperationInstanceID) 'OverThreshold'
            ,(CAST(COUNT(r.VoipOperationInstanceID) AS FLOAT) / x.TotalPolls)*100 'OverThreshold%'
          FROM VoipOperationResults r
          JOIN VoipOperations o ON o.VoipOperationInstanceID = r.VoipOperationInstanceID
          JOIN
          (
            SELECT VoipOperationInstanceID, COUNT(1) TotalPolls
            FROM VoipOperationResults
            GROUP BY VoipOperationInstanceID
          ) x ON x.VoipOperationInstanceID = r.VoipOperationInstanceID
          WHERE r.MaxRoundTripTime > 300
          AND
          r.DateTime > (GETDATE()-30)
          --ENTER YOUR OPERATION ID(s) HERE
          --AND r.VoipOperationInstanceID =
          GROUP BY o.OperationName, o.OperationType, x.TotalPolls
          

           

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services

            • Re: VNQM IPSLA Report
              taylor.greg

              Zack,

               

                   Thank you for the quick and detailed response, however I am not familiar with SQL so I am not sure what information I need to "fill in the blank". I am taking the 301 class on 10/19 so I hope to learn then, but if you are able to assist now I would greatly appreciate it as Loop1 has been instrumental in my knowledge of Solarwinds. The operation ID is 40000 across all devices.

                • Re: VNQM IPSLA Report
                  zackm

                  Sure thing!

                   

                  The Operation ID we need here is the one SW is using as the primary key in that table. The easiest way to get that, outside of SQL, would be to navigate to the operation details page and then grab it off of the URL. (In this case, the OperationID is 13)

                   

                  2015-10-07_7-58-44.jpg

                   

                  You could also gather your IDs in SQL with the following query:

                   

                  SELECT
                  OperationInstanceID
                  ,OperationName
                  ,OperationType
                  FROM VoipOperations
                  

                   

                  Once you have the ID(s), you would update the query as such: (this is assuming that you have IDs 13, 20, and 34)

                   

                  AND r.VoipOperationInstanceID IN ('13','20','34')
                  

                   

                  That would also work with a single ID, just FYI.

                   

                  Have fun at the 301 class! I've gotten a sneak peak at some of the material and this one is going to be AWESOME!

                   

                   

                  -ZackM

                  Loop1 Systems: SolarWinds Training and Professional Services

                    • Re: VNQM IPSLA Report
                      taylor.greg

                      I have run the query with my specified operation numbers and I seem to get good data but not the percent of operations there were within SLA. Is this report supposed to show this or do I still need to do some subtraction to come up with that number? I am also seeing data from more than just those specific instanceIDs. The query I put into the report is below. Am I doing something wrong? I am new to SQL hence my attendance of the 301 class.

                       

                      SELECT 

                        o.OperationName 

                        ,o.OperationType 

                        ,COUNT(r.VoipOperationInstanceID) 'OverThreshold' 

                        ,(CAST(COUNT(r.VoipOperationInstanceID) AS FLOAT) / x.TotalPolls)*100 'OverThreshold%' 

                      FROM VoipOperationResults r 

                      JOIN VoipOperations o ON o.VoipOperationInstanceID = r.VoipOperationInstanceID 

                      JOIN 

                        SELECT VoipOperationInstanceID, COUNT(1) TotalPolls 

                        FROM VoipOperationResults 

                        GROUP BY VoipOperationInstanceID 

                      ) x ON x.VoipOperationInstanceID = r.VoipOperationInstanceID 

                      WHERE r.MaxRoundTripTime > 300 

                      AND 

                      r.DateTime > (GETDATE()-30) 

                      --AND r.VoipOperationInstanceID IN = ('554','574','582','592','602','612','617','627','637','647','657','667','677','687','708','717','727','737','747','757','767','777','787','797','807','815','825','835','845','855','865')

                      GROUP BY o.OperationName, o.OperationType, x.TotalPolls 

                      Query.pngOperations.png

                        • Re: VNQM IPSLA Report
                          zackm

                          Sorry about that, I misread your original post. I thought you wanted the % of polls that were outisde of the SLA thresholds.

                           

                          This one should work. As to why you were still seeing other IP SLA Operations, on the last line, you need to remove the '--', this is a line comment marker in SQL (like # in PowerShell)

                           

                          SELECT DISTINCT
                            o.OperationName
                            ,o.OperationType
                            ,((x.TotalPolls - y.OverThreshold) / x.TotalPolls)*100 'OverThreshold %'
                          FROM VoipOperationResults r
                          JOIN VoipOperations o ON o.VoipOperationInstanceID = r.VoipOperationInstanceID
                          JOIN
                          (
                            SELECT VoipOperationInstanceID, CAST(COUNT(1) AS FLOAT) TotalPolls
                            FROM VoipOperationResults
                            GROUP BY VoipOperationInstanceID
                          ) x ON x.VoipOperationInstanceID = r.VoipOperationInstanceID
                          JOIN
                          (
                            SELECT VoipOperationInstanceID, CAST(COUNT(1) AS FLOAT) OverThreshold
                            FROM VoipOperationResults
                            WHERE MaxRoundTripTime > 300
                            GROUP BY VoipOperationInstanceID
                          ) y ON y.VoipOperationInstanceID = r.VoipOperationInstanceID
                          WHERE r.DateTime > (GETDATE()-30)
                          AND r.VoipOperationInstanceID IN = ('554','574','582','592','602','612','617','627','637','647','657','667','677','687','708','717','727','737','747','757','767','777','787','797','807','815','825','835','845','855','865')
                          
                          

                           

                           

                          Let me know how this version works out!

                           

                          Thanks.

                           

                          -ZackM

                          Loop1 Systems: SolarWinds Training and Professional Services

                            • Re: VNQM IPSLA Report
                              taylor.greg

                              That is it! I have one more tweak if not too difficult. It is only providing 10 instances, is that because the others are 100%? If so that is exactly what I am looking for!

                               

                               

                              Custom Table for Datasource 1

                               

                               

                              OPERATIONNAMEOPERATIONTYPEOVERTHRESHOLD %
                              MADISONUSB-DEFAULTICMP Echo99.44
                              BATONROUGE-DEFAULTICMP Echo99.91
                              CHICAGORPS-DEFAULTICMP Echo99.96
                              CINCINNATI-DEFAULTICMP Echo99.96
                              AUSTIN-DEFAULTICMP Echo99.91
                              BOSTONMET-DEFAULTICMP Echo99.61
                              MINNEAPOLIS-DEFAULTICMP Echo99.91
                              BOSTONPS-DEFAULTICMP Echo97.31
                              TOPEKA-DEFAULTICMP Echo99.96
                              HUNTVALLEY--DEFAULTICMP Echo99.96