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
Date 9th May 2010
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
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. ****
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.
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
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
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
WHERE (inserttime > GETDATE() - '00:05:00') AND (source = target)
ORDER BY samplevalue DESC