DX Infrastructure Manager

Expand all | Collapse all

QOS_Views SQL Script

  • 1.  QOS_Views SQL Script

    Posted 11-09-2010 04:05 AM

    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



  • 2.  Re: QOS_Views SQL Script

    Posted 09-09-2011 02:21 AM

    Latest Update to QOS_View's

     

    ------------------------------------------------
    --
    -- QOS_Views SQL Script
    --
    -- Version: 1.8
    -- Date 16 March 2011
    --
    -- 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
    -- 1.8 James Christensen - Fixed issue that if the QOS name had a '%' or a ':' or a '.' in it the View would fail to create
    --



  • 3.  Re: QOS_Views SQL Script

    Posted 12-07-2011 02:08 PM

    I've updated the Qos_Views SQL script to work with the new NIS DB format that was release with NMS 5.60.

     

    Please note that the compressed and inserttime fields are no longer valid fields have been removed from the View, so if existing Customers are using either of these fields in their queries they will need to change their query. It's unlikely that they are using the compressed field, but if they are using the inserttime field, that can be substituted for the sampletime field.

     

    ------------------------------------------------
    --
    -- QOS_Views SQL Script
    --
    -- Version: 1.9
    -- Date 07 December 2011
    --
    -- 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
    -- 1.8 James Christensen - Fixed issue that if the QOS name had a '%' or a ':' or a '.' in it the View would fail to create
    -- 1.9  Adrian Spittles - Removed compressed and inserttime fields from the View, for NIS DB V4.8 or above, these are no longer avialable in NMS 5.60
    --



  • 4.  Re: QOS_Views SQL Script

    Posted 03-14-2012 07:55 PM

    Quite q boit of work has been done to this script, specifically due to the changes in teh RN_QOS_DATA_xxxx tables and the elimination of some specific columns as part of the latest release(s) of NMS. The latest version is now 2.4.

     

    -- 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
    -- 1.8    James Christensen - Fixed issue that if the QOS name had a '%' or a ':' or a '.' in it the View would fail to create
    -- 1.9  Adrian Spittles - Removed compressed and inserttime fields from the View, for NIS DB V4.8 or above, these are no longer avialable in NMS 5.60
    -- 2.0  Adrian Spittles - Changed all Select Statement to be Non-Locking
    -- 2.1  Adrian Spittles - Added 'samplemax' field into the view for NIS DB V4.8 or above & Error trapping to catch RN tables that don't have samplemax fields
    -- 2.2  Adrian Spittles - Added 'samplemax' field into the view for NIS verisons prior to NIS DB V4.8
    -- 2.3  Adrian Spittles - Added 'inserttime' alias for NIS DB V4.8 or above
    -- 2.4  James Christensen - Added more meaningful print statements so that it can be determmined which create view try/catch worked and changed the query to determine the NIS DB version

     

    The updated script is attached. NOTE: The enhanced print statements are only useful when executing the script frm the SQL Server Management Studio GUI. They are not seen if executed from the SLM SQL query tool. If you need to run this from the SLM SQL Query Tool, Then copy the body of the script that is between the two "go" statements (not including the "go" statements) and paste it inot the SQL Query Tool window and execute it.  Also, this is a SQL Server only script and will not work with MySQL or Oracle databases. Also note, that the addition of the samplemax column is only applicable for QoS metrics that have a sample maximum, primarily ones that are a percentage. Others that are straight values, eg disk usage in MB, will not have this column.



  • 5.  Re: QOS_Views SQL Script

    Posted 03-14-2012 07:58 PM

    this post is not allowing me to attach the actual file with the script in it. I sent it out to the nimtech email group yesterday. If you need it, please contact me and I will send it via email. james.christensen2nimsoft.com



  • 6.  Re: QOS_Views SQL Script

    Posted 04-05-2012 02:49 AM

    Latest version V2.7...

     

    -- 2.5  James Christensen - Added some additional edits received from a customer (see the REPLACE staements). Also added the ability to get feedback from
    -- the script when running it from the Service Level Manager SQL Query tool. NOTE: This does not seem to run from the UMP SLM portlet -> SQL Query tool.
    -- 2.7  James Christensen - Replaced the individual character replacement blocks with REPLACE statements. Added the replacement of the following characters
    -- (+,_-_,and __). Added the creation of a temporary table and the inserting of status messages for debugging. Added a final select from the S_QOS_DATA
    -- table so the row count can be compared to the row count from the select from the temporary table to verify that all QOS objects were processed.
    --