Cognos Analytics

Expand all | Collapse all

Cognos Audit database pruning records

  • 1.  Cognos Audit database pruning records

    Posted 12 days ago
    We are planning on holding only 2 years of history in your audit db.  I haven't found much help online about pruning records in the audit database.  I come up with this, but was curious what other do.
         SQL Statement:
         delete FROM COGIPF_ACTION where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())

         delete FROM COGIPF_NATIVEQUERY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_PARAMETER where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_RUNJOB where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_RUNJOBSTEP where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_RUNREPORT where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_USERLOGON where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_VIEWREPORT where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_EDITQUERY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_AGENTRUN where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_AGENTBUILD where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_MIGRATION where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_THRESHOLD_VIOLATIONS where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_POWERPLAY where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_HUMANTASKSERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_ANNOTATIONSERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())
         delete FROM COGIPF_MOBILESERVICE where COGIPF_LOCALTIMESTAMP < DateAdd(yy, -2, GetDate())


    ------------------------------
    Tony Barlas
    ------------------------------


  • 2.  RE: Cognos Audit database pruning records

    Posted 12 days ago
    Awesome !!!, Thanks Tony for sharing this script to the community, these script are of use to me.


    ------------------------------
    Vincent Dsouza
    ------------------------------



  • 3.  RE: Cognos Audit database pruning records

    Posted 12 days ago
    No problem.  I'm surprised that it not more information about pruning records from the audit db is available out in the wild.  This scripts was designed using Cognos 11.1.7.

    ------------------------------
    Tony Barlas
    ------------------------------



  • 4.  RE: Cognos Audit database pruning records

    Posted 11 days ago
    Hi, 

    We built a similar script as below based on the tables listed in the content store cleanup scripts available in the installation folders. 

    -- This script deletes records from tables for Microsoft SQL Server database.
    -- Parameters:
    -- 1) Replace AUDITDB_SCRIPT_TIMESTAMP to the timestamp value provided by the user.
    DECLARE @AUDITDB_SCRIPT_TIMESTAMP datetime = '2018-01-01'

    delete FROM COGIPF_ACTION where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_PARAMETER where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_NATIVEQUERY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_RUNJOB where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_RUNJOBSTEP where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_RUNREPORT where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_USERLOGON where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_VIEWREPORT where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_EDITQUERY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_AGENTRUN where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_AGENTBUILD where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_MIGRATION where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_THRESHOLD_VIOLATIONS where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_POWERPLAY where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_HUMANTASKSERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_ANNOTATIONSERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP
    delete FROM COGIPF_MOBILESERVICE where COGIPF_LOCALTIMESTAMP < @AUDITDB_SCRIPT_TIMESTAMP

    There used to be a cleanup tool in earlier versions of Cognos (https://www.ibm.com/support/pages/node/315223) but now that Cognos is offering a paid Toolkit similar to Motio, BSP Metamanager etc, they removed access to all such utilities from public domain. 

    Regards,

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------



  • 5.  RE: Cognos Audit database pruning records

    Posted 11 days ago
    In Cognos 10 IBM had a cleaning tool IBM__AUDITDBCleanup, this worked fine to clean the Audit DB

    I don't know if they if they have updated the SQL-statements , in this tool, for 11.1, yet

    ------------------------------
    Paul Peeters
    ------------------------------



  • 6.  RE: Cognos Audit database pruning records

    Posted 11 days ago
    Yes, the Auditdb cleanup utility has been updated for 11.X. Please request for it so you are not deleting stuff that should be left alone!

    ------------------------------
    William Sai-Palm
    ------------------------------



  • 7.  RE: Cognos Audit database pruning records

    Posted 7 days ago
    You ever try to get stuff from IBM's diagtool?  Not terribly helpful.

    ------------------------------
    Tony Barlas
    ------------------------------



  • 8.  RE: Cognos Audit database pruning records

    Posted 11 days ago
    Hi Tony,

    thanks, this brought me to the idea to make the script more dynamic and hopefully future proven in a matter of changing tables or even column names. Please try this (unfortunately the format got corrupted), there is still some potential to optimize, please feel free to adjust.

    use CognosAudit
    GO

    DECLARE @vTablename VARCHAR(255)
    DECLARE @vColumnname VARCHAR(100)
    DECLARE runCursor_DB CURSOR
    FOR
          SELECT '['+c.[TABLE_CATALOG]+'].['+c.[TABLE_SCHEMA]+'].['+c.[TABLE_NAME]+']' AS tableName
              ,'['+c.[COLUMN_NAME]+']' AS columnName
          FROM [INFORMATION_SCHEMA].[TABLES] t
          INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] c
              ON c.TABLE_CATALOG = t.TABLE_CATALOG
          AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
          AND c.TABLE_NAME = t.TABLE_NAME
          WHERE t.TABLE_TYPE = 'BASE TABLE' --# Tabellen vs Views
          AND c.[COLUMN_NAME] like '%TIMESTAMP%'
          ORDER BY 1
        ;
    OPEN runCursor_DB;
    FETCH NEXT FROM runCursor_DB INTO @vTablename, @vColumnname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
          PRINT 'Delete records from table ' + @vTablename ;
          --Search and control validity before deleting records
          DECLARE @searchSQL VARCHAR(max)= 'SELECT count(0) c FROM ' +@vTablename+ ' WHERE ' +@vColumnname+ ' < convert(date, ''2019-01-01'', 23) '
          --DECLARE @searchSQL VARCHAR(max)= 'DELETE FROM ' +@vTablename+ ' WHERE ' +@vColumnname+ ' < convert(date, ''2019-01-01'', 23) '
          EXEC(@searchSQL);
          FETCH NEXT FROM runCursor_DB INTO @vTablename, @vColumnname;
    END;
    CLOSE runCursor_DB;
    DEALLOCATE runCursor_DB;

    ------------------------------
    Yvonne Warmbier
    ------------------------------