DX Infrastructure Manager

Expand all | Collapse all

Obtain QOS metrics from data via single query

  • 1.  Obtain QOS metrics from data via single query

    Posted 20 days ago
    Hi guys,

    I've sumbled upon this knowledge page on how to extract QOS metrics directly from the database using two queries: How do I export data from UIM for QOS metrics for a time period?

    Query one to find the r_table and table_id:
    select source, target, r_table, table_id from S_QOS_DATA where qos='QOS_CPU_USAGE' and source ='asterix' and target = 'Total'

    Second query utilising the above r_table and table_id numbers to obtain the series data:
    select sampletime, samplevalue from RN_QOS_DATA_0014 where table_id = '9' order by sampletime DESC


    Is there any way to get this in one query? I've spent hours googling and playing around with various methods to no avail.

    Cheers


  • 2.  RE: Obtain QOS metrics from data via single query

    Posted 19 days ago
    In order to achieve this generically you need the ability to execute dynamic SQL. MSSQL has an exec() function where you can achieve this but it's not one query - it would be two at a minimum - one to create the sql string and one to execute the exec() function.

    You might be able to do this with the XML functions in MSSQL too as there, you can pass a string to the xml function and it will execute the sql and return xml and then you can use the results.

    Finally someone here wrote a script that generates views for each of the QOS - called qos_views_2.7.sql (or something similar)

    The contents of a copy I have from 2017 is:

    ------------------------------------------------
    --
    -- QOS_Views SQL Script
    --
    -- Version: 2.7
    -- Date 03 April 2012
    --
    -- 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 worked and changed the query to determine the NIS DB version
    -- 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.
    --
    -- 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
    -- FROM V_QOS_CPU_USAGE
    -- WHERE (sampletime > GETDATE() - '00:05:00') AND (source = target)
    -- ORDER BY samplevalue DESC
    --
    ------------------------------------------------


    USE NimsoftSLM
    GO
    DECLARE @qos varchar(255), @pos int, @r_table varchar(255), @view varchar(255), @cview varchar(4096), @ver numeric (10,4), @msg varchar(255)
    Create table view_temp (message varchar(255))
    SELECT @ver = ModuleVersion FROM tbnVersion WITH (NOLOCK) where ModuleName = 'NIS_SLM'

    DECLARE qos_cursor CURSOR FOR
    SELECT DISTINCT qos,r_table FROM S_QOS_DATA WITH (NOLOCK)
    ORDER BY 2

    OPEN qos_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM qos_cursor INTO @qos, @r_table

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN


    -- Doctor up the QOS names removing and replacing characters that may cause issues

    select @qos= REPLACE(@qos,'+','_PLUS_')
    select @qos= REPLACE(@qos,'_-_','_')
    select @qos= REPLACE(@qos,'__','_')
    select @qos= REPLACE(@qos,'''','_')
    select @qos= REPLACE(@qos,'(','_')
    select @qos= REPLACE(@qos,')','_')
    select @qos= REPLACE(@qos,'-','_')
    select @qos= REPLACE(@qos,'%','')
    select @qos= REPLACE(@qos,':','')
    select @qos= REPLACE(@qos,'.','')
    select @qos= REPLACE(@qos,'/','_')




    -- Create view

    SET @view = 'V_'+@qos
    PRINT 'Working on view for ' +@qos
    --Select 'Working on view for '+@qos

    -- NMS V5.60 and above uses new DB version 4.800 and above. compressed & inserttime fields
    -- are missing from this new format.
    IF @ver < 4.8
    BEGIN
    SET @cview = 'CREATE VIEW '+@view+' AS '+
    'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.compressed,D.tz_offset,D.inserttime '+
    'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
    END
    ELSE
    BEGIN
    SET @cview = 'CREATE VIEW '+@view+' AS '+
    'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.tz_offset,D.sampletime as inserttime '+
    'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
    END

    BEGIN TRY
    exec ('DROP VIEW '+@view)
    END TRY
    BEGIN CATCH
    -- Suppress Error message for Views that don't exist
    END CATCH
    BEGIN TRY
    -- Create the View, Catch tables that don't have samplemax field
    exec (@cview);
    PRINT 'Created View: ' + @view
    Select @msg = 'Created View: ' + @view
    Insert into view_temp VALUES (@msg)
    END TRY
    BEGIN CATCH
    IF @ver < 4.8
    BEGIN
    SET @cview = 'CREATE VIEW '+@view+' AS '+
    'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.compressed,D.tz_offset,D.inserttime '+
    'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
    END
    ELSE
    BEGIN
    SET @cview = 'CREATE VIEW '+@view+' AS '+
    'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.tz_offset,D.sampletime as inserttime '+
    'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
    END
    BEGIN TRY
    -- Create the View, Catch tables that don't have samplestdev & samplerate fields
    exec (@cview);
    PRINT 'Created View without samplemax: ' + @view
    Select @msg = 'Created View without samplemax: ' + @view
    Insert into view_temp VALUES (@msg)
    END TRY
    BEGIN CATCH
    SET @cview = 'CREATE VIEW '+@view+' AS '+
    'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.tz_offset,D.sampletime as inserttime '+
    'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
    exec (@cview);
    PRINT 'Created View without samplemax, samplestdev and samplerate: ' + @view
    Select @msg = 'Created View without samplemax, samplestdev and samplerate: ' + @view
    Insert into view_temp VALUES (@msg)
    END CATCH
    END CATCH

    -- Useful Print statements for debuging this SQL procedure, just uncomment to use
    -- PRINT @ver
    -- PRINT 'qos: ' + @qos + ' ' + @r_table + ' ' + @view
    -- PRINT @cview

    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM qos_cursor INTO @qos, @r_table

    END

    CLOSE qos_cursor
    DEALLOCATE qos_cursor
    select * from view_temp
    SELECT DISTINCT qos,r_table FROM S_QOS_DATA WITH (NOLOCK)
    drop table view_temp
    GO




  • 3.  RE: Obtain QOS metrics from data via single query

    Posted 18 days ago
    Edited by Luc Christiaens 10 days ago
    1 - After you execute the sql_view script you can use simple queries to extract the QoS values:
    ----
    select source, target, sampletime, samplevalue
    from v_qos_disk_free_perc with(nolock)
    where source = 'server_name'
    and target = 'c:\'
    and sampletime >= dateadd(hour, -12, getdate())
    order by sampletime asc


    ----
    2 - If you are interested in the last available qos value for several qos metrics you can use following example: (this example does NOT need the sql_view script)
    ----
    Declare @source NVARCHAR(50)
    SET @source = 'bgbulab57'

    select tab1.target Target, cast(tab1a.samplevalue as decimal(30)) 'Size in GB',
    ( select cast(tab2a.samplevalue as decimal(30)) FROM S_QOS_SNAPSHOT tab2a with(nolock) JOIN S_QOS_DATA tab2 with(nolock) on tab2a.table_id = tab2.table_id
    where source = @source and qos = 'qos_disk_usage' and tab1.target = tab2.target ) as 'Used',
    ( select cast(tab3a.samplevalue as decimal(30)) FROM S_QOS_SNAPSHOT tab3a with(nolock) JOIN S_QOS_DATA tab3 with(nolock) on tab3a.table_id = tab3.table_id
    where source = @source and qos = 'qos_disk_usage_perc' and tab1.target = tab3.target ) as 'Used %',
    ( select cast(tab4a.samplevalue as decimal(30)) FROM S_QOS_SNAPSHOT tab4a with(nolock) JOIN S_QOS_DATA tab4 with(nolock) on tab4a.table_id = tab4.table_id
    where source = @source and qos = 'qos_disk_free' and tab1.target = tab4.target) as 'Free',
    ( select cast(tab5a.samplevalue as decimal(30)) FROM S_QOS_SNAPSHOT tab5a with(nolock) JOIN S_QOS_DATA tab5 with(nolock) on tab5a.table_id = tab5.table_id
    where source = @source and qos = 'qos_disk_free_perc' and tab1.target = tab5.target) as 'Free %'
    FROM S_QOS_SNAPSHOT tab1a with(nolock) JOIN S_QOS_DATA tab1 with(nolock) on tab1a.table_id = tab1.table_id
    where source = @source
    and qos = 'qos_disk_size'
    and tab1a.sampletime >= dateadd(hour, -12, getdate())
    order by target



  • 4.  RE: Obtain QOS metrics from data via single query

    Posted 6 days ago

    Thanks guys, much appreciated.

    Looks a little more complicated than i was hoping for. We're utilising Grafana to pull metrics from UIM via the UIMAPI but combining the calls with a few sql queries to fill in gaps where the api falls short.

    Was curious if it was possible to easily utilise sql queries for all of it... but clearly not!

    Thanks again