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