In TrueSight Capacity Optimization (TSCO), what SQL can be executed to force dismiss/delete a system?

Version 4
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    TrueSight Capacity Optimization


    COMPONENT:

    Capacity Optimization


    APPLIES TO:

    TrueSight Capacity Optimization 11.5 11.3.01, 11.0, 10.7, 10.5, 10.3, 10.0 ; BMC Capacity Optimization 9.5



    QUESTION:

    In our TrueSight Capacity Optimization (TSCO) environment there is a large number of systems that should be deleted from the environment. Identifying and dismissing the systems with the TSCO UI is difficult.  Is there SQL that can be run against the TSCO database itself to dismiss the entities?   If so, what is the SQL that can be run to dismiss entities from the environment?

     

     


    ANSWER:

     

    In TrueSight Capacity Optimization version 10.7 and later there is an "Automatic purging" feature that provides functionality to automatically dismiss entities after they haven't reported data for some period or time and then a way to automatically purge those entities from the Dismissed list after a longer period.

    See Enabling automatic dismissal and purging of systems and business drivers in the product  documentation

      

    https://docs.bmc.com/docs/display/btco115/Enabling+automatic+dismissal+and+purging+of+systems+and+business+drivers

      

    In TrueSight Capacity Optimization (TSCO) the process to remove an entity from the TSCO environment is to:

      
       
    1. Flag the entity as DISMISSED
    2.  
    3. Under Workspace -> All systems and business drivers -> Dismissed
    4.  
    5. click the 'Clean up all dismissed object button.
      

    That will trigger the internal cleanup of the entity within TSCO which will remove all the data associated with the entity and delete it from the environment.

      

    Can entities be flagged as dismissed by running SQL against the TSCO database?

      

    WARNING: Running SQL to dismiss entities from the TSCO database is outside of the scope of Technical Support and could result in data loss if done incorrectly.  A full backup of the TSCO database should be available before dismissing entities and careful attention must be paid to the entities being selected for DISMISSED status when the SQL is executed.

    To flag an entity as DISMISSED via SQL run against the TSCO database as the TSCO Database Schema Owner you can run the following SQL:

      

    update sys_def set statusid=4 where name='XXXX';
    commit;

      


    where XXXX is the system name

    OR

      

    update sys_def set statusid=4 where sysid=XXXX;
    commit;

      


    where XXXX is the system id

      

    For example, to update all the system that have not importing data into TSCO for the last 365 days to DISMISSED (so they can then be cleaned via the "Clean up all dismissed objects" button one can use a SQL statement similar to the following:

      

    update sys_def set statusid=4 where ACTIVITYDATE < sysdate-365

      

    Note that once an entity has been cleaned up from the dismissed objects list there is no way to restore the data for that entity beyond a restore of the entire BCO database.  So it is critical to be sure that only the expected systems have been flagged as dismissed before running the Clean up all dismissed object.  Manually running SQL directly against the TSCO database to dismiss objects involves risk and the SQL and results should be carefully reviewed before changing the status of any entity within the TSCO database.

    For additional information contact TrueSight Capacity Optimization Technical Support.

    Q: How can I move all entities that reside in the Unassigned list to the Dismissed list?

    Entities that reside in the Unassigned List will have a SYS_DEF.STATUSID of '2'.

    So, to get a list of all the entities in the Unassigned List you could run the following SQL:

      select * from sys_def where statusid=2; -- (2 is for unassigned, 4 is for dismissed)

    Next, to move all of the entities from the Unassigned List to the Dismissed list you could run the following SQL:

      update sys_def set statusid=4 where statusid=2;
      commit;

    To be more targeted in which systems one moved from the Unassigned domain to the Dismissed list additional parameters could be added to the 'where' clause for that SQL.

    For example, to only move entities from Unassigned to Dismissed which haven't reported data in more than 60 days:

      update sys_def set statusid=4 where statusid=2 and activitydate < sysdate-60;
      commit;

       Although less common, it is possible for the Unassigned list to include Applications (workspace domain folders) and Business Drivers as well that may need to be cleaned up.

    The following SQL will detect if there are Applications or Business Drivers in the Unassigned list:
      SELECT COUNT(*) FROM APPLICATION WHERE STATUSID = 2;
      SELECT COUNT(*) FROM WKLD_DEF WHERE STATUSID = 2;
    .
    If there are STATUSID = 2 workloads or business drivers those could also be flagged as dismissed:
      SELECT * FROM APPLICATION WHERE STATUSID = 2;  -- SQL to list them
      UPDATE APPLICATION SET STATUSID = 4 WHERE STATUSID = 2; -- SQL to move them to dismissed list

      SELECT * FROM WKLD_DEF WHERE STATUSID = 2;  -- SQL to list them
      UPDATE WKLD SET STATUSID = 4 WHERE STATUSID = 2; -- SQL to move them to dismissed list


      

     


    Article Number:

    000257718


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles