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

# VNQM IPSLA Report

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

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)
--AND r.VoipOperationInstanceID =
GROUP BY o.OperationName, o.OperationType, x.TotalPolls
```

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

• ###### Re: VNQM IPSLA Report

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

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

• ###### Re: VNQM IPSLA Report

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

• ###### Re: VNQM IPSLA Report

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

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 %
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

• ###### Re: VNQM IPSLA Report

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

• ###### Re: VNQM IPSLA Report

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