VNQM IPSLA Report

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

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems

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.

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)

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

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems

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 

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

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems

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!

Created on 10/13/2015, SolarWinds Worldwide, LLC. All Rights Reserved.

I'm not sure, I do not have anything in there for "Top 10". Off the top of my head, I would suspect that the ones missing do not have any instances where the MaxRoundTripTime was > 300

I am going to go with that then. Zack this is more than I could have hoped for. Thank you so much! Loop1 rocks!!!