Adrian.spittles

QOS_Views SQL Script

Discussion created by Adrian.spittles on Nov 9, 2010
Latest reply on Apr 5, 2012 by spiad01

This may not be used much anymore as we have the List Viewer in UMP now, but if you want to embed a table in your dashboard, then it may still be of use...

 

QOS_Views SQL Script

 

Version:               1.7

Date      9th May 2010

 

Version history:

1.0          Remko Dobber - This is the first release

1.1          James Christensen - Added Origin field for better multi-tenancy support

1.2          Adrian Spittles - Fixed issue that if the QOS name had a '-' in it the View would fail to create

1.3          Adrian Spittles - Added Robot and Probe fields for backward compatibility support with QOS_Server probe

1.4          Adrian Spittles - Fixed issue that if the QOS name had a '_' in it the View would fail to create

1.5          Adrian Spittles - Fixed issue that if the QOS name had a '(' or a ')' in it the View would fail to create

1.6          Adrian Spittles - Added Error trapping for Drop statement for Views that Don't Exist

1.7          Adrian Spittles - Added Error trapping to catch RN tables that don't have samplestdev & samplerate fields

 

Description:

This sql procedure  generates easy-to-use views in the NIS for accessing raw qos data.

The intention of this script is that is is used instead of the qos_server custom probe.

The Script creates views named V_qosname, for example V_QOS_CPU_USAGE.

 

**** Please note that this is not supported by Nimsoft Support. This Script is intended to

be used as is, feel free to modify for your own purpose. ****

 

Install Notes:

Check the name of your Nimsoft SLM database, it may be called NimbusSLM or NimsoftSLM

Modify the the first line of the Script "USE NimsoftSLM" to reflect the correct DB name.

 

Run the script in SQL Management Studio and it will create the views based on the current

contents of S_QOS_DATA.

 

The first time the script is run you will see a whole bunch of error similar to the one below,

this is normal as the views cannot be dropped as they have not been created before. Run the script

a second time and the errors should have gone away.

Error: "Cannot drop the view 'V_QOS_xxxxxxxxxx', because it does not exist..."

 

If new qos types are add added to the S_QOS_DATA table then this script will need to be rerun.

 

Usage Examples:

When you want to see the raw CPU data for a server called Apollo, use a query like this:

 

SELECT * FROM V_QOS_CPU_USAGE

WHERE source='Apollo'

 

To get the average, minimum and maximum values measured in the past hour, use this:

 

select AVG(samplevalue) as avg ,MIN(samplevalue) as min,MAX(samplevalue) as max

from V_QOS_CPU_USAGE

where sampletime>GETDATE()-'01:00:00'

order by 1 desc

 

Top 20 servers sorted by CPU Usage, assuming data is being collected every 5 mins

 

SELECT     TOP (20) source, target, sampletime, samplevalue, samplestdev, samplerate, inserttime

FROM         V_QOS_CPU_USAGE

WHERE     (inserttime > GETDATE() - '00:05:00') AND (source = target)

ORDER BY samplevalue DESC

Outcomes