Preparing for offline database cleanup

Before you run Offline Cleanup, review the following preparatory tasks and perform the tasks that are necessary for your environment:

  • To use the Offline Cleanup utility on Red Hat Linux, you must have several 32-bit library files (.i386 or .i686 extension) installed.

    Click here for the list of library files.
  • To maintain data consistency, ensure that all Application Servers are shut down and that no ETL processes are running on the database.
  • Ensure that the Application Server is upgraded to TrueSight Server Automation version 8.3 SP2 or later.
  • Ensure that the database statistics are updated before running the wizard.
  • BMC recommends that you run an ETL for data older than the retention period that you want to store in the Reporting Data Warehouse. After Cleanup is run, the data will not be available to load into the Data Warehouse.
  • Back up the database before you begin the cleanup.
  • You can choose to upgrade the database either before or after performing Offline Cleanup.
  • Ensure that the database user is granted the permissions required by TrueSight Server Automation. For the list of permissions, see List-of-required-database-permissions. For offline database cleanup, the database user is required to have the following additional permissions:
    • Oracle. The Oracle DBA must ensure that the BladeLogic database user has the appropriate permissions to run the Offline Cleanup:
      • Create Job
        Syntax: GRANT CREATE JOB TO <bsa schema user>
      • Create External Job
        Syntax: GRANT CREATE EXTERNAL JOB TO <bsa schema user>
      • Execute on DBMS scheduler
        Syntax: GRANT EXECUTE ON DBMS_SCHEDULER TO <bsa schema user>
    • SQL Server:
      • The database user should have sysadmin role.
        Syntax: EXEC master..sp_addsrvrolemember @loginame = <bsa db user>, @rolename = 'sysadmin'
      • SQL Server Agent Service is running.

  • Prior to running the cleanup process, it is worthwhile to capture the current size of the database tables so that these sizes can be compared to the post-cleanup sizes. To capture database table sizes, run one of the provided scripts, depending on your database type — either Oracle or SqlServer.

    Note

    Because the Oracle script queries the USER_TABLES to determine the size and row counts, ensure that a table statistics run has completed prior to running the analysis script.

    For various other methods for checking up on the state of your database, see Determining-the-state-of-the-database-before-or-during-cleanup.

    For a list of database tables that are impacted by the cleanup process and their correlation with the Offline Cleanup modules, expand the following table.

    Module name

    Table name

    Job Run Event

    JOB_RUN_EVENT

    Job Run Event

    DEPLOY_JOB_RUN_EVENT

    Snapshot

    SNAPSHOT_AUTH_DEPLOY_JOB_RUNS

    Snapshot

    SNAPSHOT_COMPONENT_STATS

    Snapshot

    SNAPSHOT_PART

    Snapshot

    SNAPSHOT_GENERIC_ASSET

    Snapshot

    SNAPSHOT_SYSTEM_INFO_OBJECT

    Snapshot

    SNAPSHOT_EVENT_LOG

    Snapshot

    SNAPSHOT_DOT_NET_ASSEMBLY

    Snapshot

    SNAPSHOT_COM_OBJECT_CNT

    Snapshot

    SNAPSHOT_METABASE_OBJECT_CNT

    Snapshot

    SNAPSHOT_BLFILE_CNT

    Snapshot

    SNAPSHOT_GPO_SETTING

    Snapshot

    SNAPSHOT_VMWARE_OBJ_CNT

    Snapshot

    SNAPSHOT_CFG_FILE_OBJECT_CNT

    Snapshot

    SNAPSHOT_COM_OBJECT

    Snapshot

    SNAPSHOT_GPO_SETTING_CNT

    Snapshot

    SNAPSHOT_METABASE_OBJECT

    Snapshot

    SNAPSHOT_BLFILE

    Snapshot

    SNAPSHOT_OS_USER

    Snapshot

    SNAPSHOT_OS_GROUP

    Snapshot

    SNAPSHOT_CONFIG_FILE_OBJECT

    Snapshot

    SNAPSHOT_SOFTWARE

    Snapshot

    SNAPSHOT_SERVICE

    Snapshot

    SNAPSHOT_SYS_INFO_OBJECT_CNT

    Snapshot

    SNAPSHOT_REGISTRY_OBJECT

    Snapshot

    SNAPSHOT_REGISTRY_OBJECT_CNT

    Snapshot

    SNAPSHOT_VMWARE_OBJECT

    Compliance

    COMPLIANCE_REMEDIATION_RUN

    Compliance

    CR_GROUP_RESULT

    Compliance

    JOB_RESULT_CR_GROUP_RESULT

    Compliance

    COMP_RULE_GROUP_RESULT_XREF

    Compliance

    COMPLIANCE_RULE_RESULT

    Compliance

    COMP_RULE_GROUP_RULE_RESULT

    Compliance

    CONDITION_RESULT

    Compliance

    CR_RULE_RESULT_SELECTOR

    Compliance

    COMP_ENTRY_RESULT

    Compliance

    COMP_ENTRY_RESULT_DETAIL

    Compliance

    COND_RESULT_SUB_COND_RESULT

    Compliance

    PROPERTY_CONDITION_RESULT

    Compliance

    ASSET_ATTRIBUTE_COND_RESULT

    Compliance

    ASSET_SELECTOR_RESULT

    Compliance

    SELECTOR_CONDITION_RESULT

    Compliance

    SELECTOR_RESULT_PATH

    Compliance

    HRB_COMP_RULE_GROUP_RESULT

    Compliance

    HRB_CONDITION_RESULT

    Compliance

    SCAP_RESULT_TARGET_DATA

    Compliance

    SCAP_RESULT_NETWORK_DATA

    Compliance

    SCAP_RESULT_RULE_DATA

    Deploy

    DEPLOY_JOB_RUN

    Deploy

    DEPLOY_JOB_RUN_CLASS_REF

    Deploy

    DEPLOY_JOB_RUN_ATTRIBUTE_REF

    Deploy

    JOB_RESULT_GEN_DEPOT_OBJECT

    Deploy

    DEPLOY_JOB_DEVICE_RUN

    Patching

    PATCHING_JOB_RESULTS_ASSOC

    Patching

    PATCHING_JOB_RUN_PART

    Patching

    PATCH_RMD_DNL_JOB_RUN_ASSOC

    Patching

    PATCH_CUJ_RESULT

    Patching

    BL_PATCH_JOB_RESULT_COUNT

    Patching

    BL_PATCH_JOB_RES_PATCH_COUNT

    Patching

    PATCH_DEPENDENCY_MAP

    Patching

    BL_PATCH_ANALYSIS_RES_DATA

    Patching

    BL_PATCH_RES_DEVICE_CNT

    Patching

    BL_PATCH_DOWNLOAD_RES_DATA

    Patching

    PATCH_DOWNLOAD_RESULT

    Patching

    PATCH_RMD_RESULT

    Patching

    PAJOB_RUN_CATALOG_ASSOC

    Patching

    PATCH_RMD_JOB_ASSOC

    Audit

    AUDIT_OBJECT

    Audit

    AUDIT_RESULT_PART_RULE_SET

    Audit

    AUDIT_OBJECT_PART

    Audit

    AUDIT_GPO_SETTING_CNT

    Audit

    AUDIT_REGISTRY_OBJECT_CNT

    Audit

    AUDIT_COM_OBJECT_CNT

    Audit

    AUDIT_OS_USER

    Audit

    AUDIT_CONFIG_FILE_OBJECT

    Audit

    AUDIT_DOT_NET_ASSEMBLY

    Audit

    AUDIT_TARGET_PART_STATUS

    Audit

    AUDIT_COM_OBJECT

    Audit

    AUDIT_METABASE_OBJECT_CNT

    Audit

    AUDIT_GPO_SETTING

    Audit

    AUDIT_EVENT_LOG

    Audit

    AUDIT_CFG_FILE_OBJECT_CNT

    Audit

    AUDIT_VMWARE_OBJECT

    Audit

    AUDIT_OBJECT_PART_COUNT

    Audit

    AUDIT_BLFILE

    Audit

    AUDIT_SOFTWARE

    Audit

    AUDIT_BLFILE_CNT

    Audit

    AUDIT_VMWARE_OBJ_CNT

    Audit

    AUDIT_SYS_INFO_OBJECT_CNT

    Audit

    AUDIT_REGISTRY_OBJECT

    Audit

    AUDIT_SYSTEM_INFO_OBJECT

    Audit

    AUDIT_SERVICE

    Audit

    AUDIT_METABASE_OBJECT

    Audit

    AUDIT_OS_GROUP

    Audit

    AUDIT_GENERIC_ASSET

    Audit Trail

    AUDIT_TRAIL

    Audit Trail

    APP_SERVER_AUDIT_TRAIL

    Audit Trail

    AUTO_PRINCIPAL_AUDIT_TRAIL

    Audit Trail

    BL_ACL_POLICY_AUDIT_TRAIL

    Audit Trail

    BL_ASSET_CLASS_AUDIT_TRAIL

    Audit Trail

    BLGROUP_AUDIT_TRAIL

    Audit Trail

    BLROLE_AUDIT_TRAIL

    Audit Trail

    BLUSER_AUDIT_TRAIL

    Audit Trail

    BL_ACL_TEMPLATE_AUDIT_TRAIL

    Audit Trail

    BL_AUTH_PROFILE_AUDIT_TRAIL

    Audit Trail

    COMPONENT_AUDIT_TRAIL

    Audit Trail

    CUSTOM_COMMAND_AUDIT_TRAIL

    Audit Trail

    DEPOT_OBJECT_AUDIT_TRAIL

    Audit Trail

    DEVICE_AUDIT_TRAIL

    Audit Trail

    EXECUTION_TASK_AUDIT_TRAIL

    Audit Trail

    EXTENDED_OBJECT_AUDIT_TRAIL

    Audit Trail

    JOB_AUDIT_TRAIL

    Audit Trail

    LDAP_CONNECTION_AUDIT_TRAIL

    Audit Trail

    LDAP_QUERY_AUDIT_TRAIL

    Audit Trail

    PM_DEVICE_AUDIT_TRAIL

    Audit Trail

    PROP_SET_CLASS_AUDIT_TRAIL

    Audit Trail

    PROP_SET_INSTANCE_AUDIT_TRAIL

    Audit Trail

    ROUTING_POLICY_AUDIT_TRAIL

    Audit Trail

    SERVER_CONFIG_DEF_AUDIT_TRAIL

    Audit Trail

    SYSTEM_PACKAGE_AUDIT_TRAIL

    Audit Trail

    TEMPLATE_AUDIT_TRAIL

    Audit Trail

    TOKEN_RULE_SET_AUDIT_TRAIL

    Shared Data Objects

    CONFIG_FILE_OBJECT

    Shared Data Objects

    CONFIG_FILE_OBJECT_VALUE

    Shared Data Objects

    OBJECT_OVERFLOW

    Shared Data Objects

    OBJECT_OVERFLOW_DETAIL

    Job Run Framework

    JOB_RUN

    Job Run Framework

    JOB_RUN_OVERRIDDEN_PSI

    Job Run Framework

    JOB_RUN_ETL_CDC

    Job Run Framework

    NIMBUS_TEMPLATE_INSTANCE

    Job Run Framework

    JOB_RESULT

    Job Run Framework

    JOB_RUN_SCHEDULE

    Job Run Framework

    JOB_RESULT_VSMDISCOVERY

    Job Run Framework

    JOB_RESULT_DEPOT_OBJECT

    Job Run Framework

    JOB_RESULT_TEMPLATE

    Job Run Framework

    JOB_RESULT_DEVICE

    Job Run Framework

    JOB_RESULT_ATRIUM_TO_BL_SYNC

    Job Run Framework

    JOB_RESULT_BLGROUP

    Job Run Framework

    JOB_RESULT_COMPONENT

    Job Run Framework

    NTISAN_NIC

    Job Run Framework

    NTILAN_NIC

    Job Run Framework

    NTIBOOT_ORDER

    Job Run Framework

    NTIPOLICY

    Job Run Framework

    NTIVMNIC_CONFIG

    Job Run Framework

    NTIAUDIT_TRAIL

    Job Run Framework

    BATCH_JOB_RUN

    Job Run Framework

    EXECUTION_TASK_JOB_RUN

  • To evaluate the impact of the cleanup process on the TrueSight Server Automation job data that you have stored in your database, you can use the dbm_cli command line utility. Use the following information modes:

    • To evaluate how much data would be cleaned out with various retention times, run the following command:

      ./dbm_cli.nsh -eval
      For a sample output to this command, click to expand.
      |------------------------------------------------------------------------------------------------|
      Evaluating Retention Scenarios
      |------------------------------------------------------------------------------------------------|
      MODULE \ RETENTION --> 15 DAYS 60 DAYS 90 DAYS 180 DAYS 365 DAYS 730 DAYS
      Job Run Event -2 -2 -2 -2 -2 -2
      Snapshot 100 98 97 94 88 77
      Compliance 99 95 93 84 67 32
      NSH Script 98 91 86 67 41 24
      Deploy 98 94 92 82 63 30
      Patching -1 -1 -1 -1 -1 -1
      Audit 71 0 0 0 0 0
      Audit Trail -2 -2 -2 -2 -2 -2
      -----NOTE-----
      * Figures denote % of deletable data for each module
      * "-1" denotes no data available
      |------------------------------------------------------------------------------------------------|

      This sample output indicates the following findings:  

      • Audit Trail and Job Run Event modules are not evaluated for cleanup percentages. This is indicated by the number -2 (Not applicable).
      • Patching data was not available for any of the retention periods. This is indicated by the number -1 (No data available).
      • Audit data is between 15 to 60 days old.
      • Other tables would greatly benefit from a cleanup with a retention policy below 90 days (90% or more of the data would be removed).

      Note

      The calculated retention scenarios are based on statistics from job run data. To save time when you run an evaluation, statistics are refreshed only if the most recent calculation was performed more than 15 days ago. Therefore, if you run an evaluation within 15 days after a previous evaluation, during a period of normal (or heavy) usage of TrueSight Server Automation, evaluation results might not be fully accurate. For more accurate estimates, ensure that you run your evaluation more than 15 days since the last evaluation was run.

      You can, alternatively, access a similar report through the GUI — through the Settings panel of the Database Maintenance Wizard. See Running offline database cleanup.

    • To determine what jobs and job runs would be affected by various retention times, run a command similar to the following example command:

      ./dbm_cli.nsh -impact Snapshot=30

      This command is useful for determining whether the retention value passed during offline cleanup would remove any job runs that might need to be kept, as well as to see which specific jobs would be affected by the cleanup.

      For an example of the generated CSV file, click to expand.

      The CSV file contains a list of jobs and job runs affected by the cleanup, in the following format:

      JOB TYPE,OWNER,JOB NAME, RETENTION,CREATION DATE, LAST RUN
      "Snapshot Job","BLAdmin","Inventory Snapshot","null","2014-04-23 13:03:52","2014-09-30 17:00:00"

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*