DX Infrastructure Management

Expand all | Collapse all

clean up qos-objects automatically

Jump to Best Answer
  • 1.  clean up qos-objects automatically

    Posted 01-05-2011 04:54 PM

    Hi,

     

    as you know, you can define the retention period for qos-values in the data_engine. So after a certain amount of time, the qos-values will be deleted.

     

    But there is one problem that might remain:

    Let's imagine you monitor disk capacity for drive H: on a windows server called "serverA". You would then find qos-values for source=serverA, target=H: in your qos-tables.

    Now, this drive no longer exists and the probe stopped collecting the values, after your retention period is over, those values will be deleted.

    But in the Service Level Manager as well as in the Performance Report Designer / QosChart-Portlet in UMP, you will find a entry for serverA -> H: but without any values. That entry ( in the s_qos_data-table by the way) is a orphan.

     

    You might want to clean up such data on a regular basis, especially when you did a lot of testing with some probes but in the end did not collect all QoS-metrics for production use.

     

    For this purpose, I wrote a small lua-script which you can run in your environment. It is built to be run inside the Nimsoft Alarm Server (nas) and could be scheduled to be run automatically.

     

    All you need to adapt is to modify the header of the lua-script:

    - change iDays to the amount of days after which you want to get rid of qos-definitions. 7 means that 7 days after the last value was received for this Source/Target-combination, the data will be deleted.

    - modify sDatabaseType to mysql if you're running mySql as the sql-queries are slightly different. If you run oracle, let me know and I will adapt the script with the correct statement for oracle.

     

    NOTE: when you run this script the first time, the runtime might be quite long (many hours even). In this case, please do NOT execute the script directly but modify the script header to bHot = 0, the script will then print out SQL-statements which you can execute manually on your sql-commandline of your choice to clean up the database.

     

    It is suggested that you run the script with bHot=0 at least for the first time and test the statements with a qos-type which is not critical for you. Please also make sure you have a backup of your database just in case you end up deleting qos-values you did not want to delete.

     

    have fun!

    chris



  • 2.  Re: clean up qos-objects automatically

    Posted 03-02-2011 09:04 AM

    OMG You are my HERO!!!! I have been doing this manually by Remote Desktop to our NOD server and biting teeth when I get disconnected when I leave the PC for a few horus haha

     

    We try new probes and get some values but get orphans all the time (lol our own fault haha) when moving stuff around.  However our retention period is 180 days for stuff we dont move around.  This was an AWESOME script!!

     

    You ever come to Vegas sir send me an email and I will buy you a beer!!

     

     



  • 3.  Re: clean up qos-objects automatically

    Posted 10-30-2012 03:37 PM

    I found myself with a similar stuation.

     

    Lost of QoS definitions that were no longer updated making it hard to find the right QoS.

     

    And data that was no longer used.

     

    I did not see this script so I wrote a quick-and-dirty one in perl.

     

    It selects QoS definition matching some critiria

     

    Then it looks when the QoS data was last updated

     

    If this is more then 21 days it deleted the QoS definition and then the QoS data

     

    Cheers,

     

    Michel

     

     

     

     



  • 4.  Re: clean up qos-objects automatically

    Posted 02-04-2015 12:27 PM

    is that script relevant to nimsoft 8.1?

     

    regards



  • 5.  Re: clean up qos-objects automatically

    Posted 02-04-2015 01:02 PM

    I have not tested it against 8.1 but as far as I know 8.1 does not contain any change to the handling of qos_data.

    So, yes - I believe this should still be relevant in 8.1

     

    -chris



  • 6.  Re: clean up qos-objects automatically

    Posted 06-12-2015 11:56 AM

    Hi,

     

    You might want to try the following SQL script to clean up a host:

     

    --------------------

     

    DECLARE @source NVARCHAR(500);

     

    -- Set the hostname you which to delete

    SET @source = '<hostname for deletion>';

       

    DECLARE @table_id VARCHAR(256);

    DECLARE @r_table VARCHAR(256);

    DECLARE @h_table VARCHAR(256);

    DECLARE @v_table VARCHAR(256);

    DECLARE @message varchar(80);

    DECLARE @RSQLQuery AS NVARCHAR(500);

    DECLARE @HSQLQuery AS NVARCHAR(500);

    DECLARE @VSQLQuery AS NVARCHAR(500);

    DECLARE @ParameterDefinition AS NVARCHAR(100);

    DECLARE @cs_id AS NVARCHAR(500);

    DECLARE @dev_id AS NVARCHAR(500);

     

    -- selecting the cs_id from the COMPUTER_SYSTEM table

    SELECT @cs_id = cs_id from NimsoftSLM.dbo.CM_COMPUTER_SYSTEM where name = @source;

     

    -- Creating a cursor and fill it with the data from CM_DEVICE table

    DECLARE device_cursor CURSOR FOR

    SELECT dev_id from NimsoftSLM.dbo.CM_DEVICE where dev_name = @source;

     

    -- Creating a cursor and fill it with the data from the S_QOS_DATA table

    DECLARE TABLE_CURSOR CURSOR FOR

    SELECT table_id, r_table, h_table, v_table FROM NimsoftSLM.dbo.S_QOS_DATA where source = @source;

     

    -- Open the cursor

    -- Loop through the data from the S_QOS_TABLE

    -- delete all data that was collected for the system

    OPEN TABLE_CURSOR

    FETCH NEXT FROM TABLE_CURSOR

    INTO @table_id, @r_table, @h_table, @v_table

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

     

        -- Building the actual deletion queries

        SET @RSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' +  @r_table + ' where table_id = ' + @table_id;

        SET @HSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' +  @h_table + ' where table_id = ' + @table_id;

        SET @VSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' +  @v_table + ' where table_id = ' + @table_id;

        SET @ParameterDefinition =  '@table_id VARCHAR(256)'

     

        -- Executing the queries

        -- Printing a statement of what we are running

        PRINT '  '

        SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @r_table

        PRINT @message

     

        EXECUTE sp_executesql @RSQLQuery, @ParameterDefinition, @table_id

       

        PRINT '  '

        SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @h_table

        PRINT @message

       

        EXECUTE sp_executesql @HSQLQuery, @ParameterDefinition, @table_id

     

        PRINT '  '

        SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @v_table

        PRINT @message

     

        EXECUTE sp_executesql @VSQLQuery, @ParameterDefinition, @table_id

     

     

        -- Get the next set of data from the cursor

        FETCH NEXT FROM TABLE_CURSOR

        INTO @table_id, @r_table, @h_table, @v_table

     

    END

    -- Close and remove the cursor for the S_QOS_TABLE data

    CLOSE TABLE_CURSOR;

    DEALLOCATE TABLE_CURSOR;

     

    -- Delete data from the tables that we do not need anymore

    DELETE FROM NimsoftSLM.dbo.S_QOS_DATA where source = @source;

    DELETE FROM NimsoftSLM.dbo.CM_COMPUTER_SYSTEM where name = @source;

     

    -- As thers is also remaining data in the CM_DEVICE table clean that using the device_cursor

    OPEN device_cursor

    FETCH NEXT FROM device_cursor

    INTO @dev_id

    WHILE @@FETCH_STATUS = 0

    BEGIN

        -- Printing a status message

        PRINT '  '

        SELECT @message = '--- Running cleanup for device id: ' + @dev_id

        print @message

        -- Deleting data from the DEVICE tables

        DELETE FROM NimsoftSLM.dbo.CM_DEVICE_ATTRIBUTE where dev_id = @dev_id;

        DELETE FROM NimsoftSLM.dbo.CM_DEVICE_RELATIONSHIP where parent_dev_id = @dev_id;

        DELETE FROM NimsoftSLM.dbo.CM_DEVICE_RELATIONSHIP where child_dev_id = @dev_id;

     

        DECLARE @ci_id AS NVARCHAR(500);

       

        -- We need an inner cursor to clean up the CONFIGURATION ITEMS (CI)

     

        DECLARE CI_cursor cursor FOR

        SELECT ci_id from NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where dev_id = @dev_id

           

        OPEN CI_cursor

        FETCH NEXT FROM CI_cursor

        INTO @ci_id

        WHILE @@FETCH_STATUS = 0

            BEGIN

            DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM_METRIC where ci_id = @ci_id;

            DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where ci_id = @ci_id

           

        FETCH NEXT FROM CI_cursor

        INTO @ci_id

       

        END

        -- Close and cleanup the inner cursor so it can be reused in the next outer cursor loop

        CLOSE CI_cursor;

        DEALLOCATE CI_cursor;

        DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where dev_id = @dev_id;

       

        FETCH NEXT FROM device_cursor

        INTO @dev_id

       

    END

    -- Close and clean the outer cursor

    CLOSE device_cursor;

    DEALLOCATE device_cursor;



  • 7.  Re: clean up qos-objects automatically

    Posted 06-15-2015 08:25 AM

    Hi I'm looking for Chris' LUA Skript. Can anyone help me, I can't see it



  • 8.  Re: clean up qos-objects automatically

    Posted 06-16-2015 11:14 AM


  • 9.  Re: clean up qos-objects automatically

    Posted 06-17-2015 04:48 AM

    Many thanks, also for the other stuff



  • 10.  RE: Re: clean up qos-objects automatically

    Posted 10-28-2019 08:42 PM
    Sorry for the old post resurrection but is this still the best way to remove orphaned QOS from UIM SQL Db?

    Will this this script work for 9.20?

    It would be nice if there was an option in data_engine to do this, or an official script in NAS.

    cheers

    matt


  • 11.  RE: Re: clean up qos-objects automatically
    Best Answer

    Posted 10-29-2019 07:18 PM
    Hi Matt,

    I just tested this in UIM v9.20 and it worked in my environment. I ran the script via the nas. But as always, we recommend working with a DBA to assist and testing it out in a Test/DEV environment first and on a small scale, e.g., e.g., set the days to 1 and check the 'before and after' results.

    iDays = 30 -- delete qos-sources which did not deliver values in the past X days. Only specify FULL days here.
    sDatabaseType = "sqlserver" -- specify the database type you're using. Valid values: sqlserver | mysql
    -- if you have oracle, send me an e-mail
    bHot = 0 -- set this to 0 if you do not want the script to perform any real modifications, set it to 1 to run it.

    -- do not modify after this line unless you know what you're doing --
    database.open("provider=nis;database=nis;driver=none") -- open the database

    -- first, find all used RN_TABLES
    sSql = "select distinct r_table from s_qos_data"
    tRtables = database.query(sSql )

    if bHot == 0 then
    print( "-- table count: "..#tRtables.." RN-Tables" )
    print( "-- the following output is valid SQL, you can copy this and execute it in your database shell manually" )
    else
    print( "housekeeping will delete data from "..#tRtables.." RN-Tables" )
    end

    -- now iterate over all rn_tables and determine orphaned qos-data
    for iIx, tRow in pairs(tRtables) do
    sDelQuery = ""
    if sDatabaseType == "sqlserver" then
    sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having datediff(day, max(sampletime), getdate() ) > "..iDays.." );"
    end
    if sDatabaseType == "mysql" then
    sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having max(sampletime) < date_add( now(), INTERVAL -"..iDays.." DAY);"
    end

    if bHot ~= 1 then
    print( sDelQuery )
    else
    print( "deleting orphaned qos-data for table "..tRow["r_table"] )
    database.query(sDelQuery)
    end

    sDelUndeliveredQuery = "delete from s_qos_data where r_table='"..tRow["r_table"].."' and table_id not in (select distinct table_id from "..tRow["r_table"]..")"
    if bHot ~= 1 then
    print( sDelUndeliveredQuery )
    else
    print( "deleting never delivered qos-data for table "..tRow["r_table"] )
    database.query( sDelUndeliveredQuery )
    end
    end

    print( "-- housekeeping done." )

    database.close()

    ------------------------------
    [Designation]
    [City]
    ------------------------------



  • 12.  RE: Re: clean up qos-objects automatically

    Posted 10-30-2019 12:57 AM
    Thanks Stephen,

    much appreciated.