HOWTO: Clear certain entries from the tbllogs table in an Ivanti Workspace Control Database

Version 19

    Question

    The table dbo.tbllogs exists of entries from Security Management, Performance Management and the Error Log. It happens that a certain node in Security Management has caused gigabytes of logging after i.e. running in learning mode for quite some time.

     

    When having a very large database, this is mostly caused by a large dbo.tbllogs. Imagine that the table is 40GB, the Clear all logs function in the Workspace Control Console will run for a very long time or will even cause the Workspace Control Console to hang. And top of that, all logging will be lost. It`s not possible to delete the logging per node.

     

    Answer

    DANGER: Be aware that editing the Workspace Control datastore via the Microsoft SQL Management Console can potentially damage the Workspace Control environment and should only be done when you are an experienced user/database administrator and have made current backups of your system, and are knowledgeable in how to use the Microsoft SQL Management Console and Query Analyzer. Ivanti can not be held responsible for damage done to your database when you use this article.

     

    On a Microsoft SQL based database, it is possible to run a delete statement or truncate the entire table. A delete statement will delete the specified data row by row. This is the statement used by the Workspace Control Console. A truncate will mark all data as empty for reuse, which goes much faster than a delete statement.

     

     

    Query database for the largest dataset

     

    Before clearing entries from the logging database you need to determine what the largest set of data is. The following SQL query can help you in this

     

    select lngClassID ,CASE lngClassID
                WHEN '23' THEN 'Security Management: Applications'
                WHEN '24' THEN 'Security Management: Files and Folders'
                WHEN '25' THEN 'Security Management: Read-Only Blanketing'
                WHEN '26' THEN 'Security Management: Removable Disks'
                WHEN '54' THEN 'Security Management: IP Connections'
                WHEN '28' THEN 'Performance Management: Access Balancing'
                WHEN '30' THEN 'Performance Management: CPU Optimization'
                WHEN '31' THEN 'Performance Management: Memory Optimization'
                WHEN '32' THEN 'Performance Management: Instant Logoff'
                WHEN '46' THEN 'Error log'
                WHEN '47' THEN 'Workspace Analysis: User Event Log'
                WHEN '48' THEN 'User Settings Sampling mode data'
                WHEN '83' THEN 'Action task entries in the User Event Log'
             ELSE 'No description found'
          END
          ,Count(lngClassID) AS 'NR of records'
          from dbo.tblLogs  GROUP BY lngClassID 
    

     

     

     

    Query database for records that should be cleaned up

     

    Every day obsolete records are cleared by the first Workspace Control agent that connects to the database. The time of cleanup is configured in Remove obsolete log files every day at Setup > Advanced Settings.

    In some scenarios, it might happen that a database is so large, that the cleanup task cannot be completed and obsolete records start building up. Note that during the day there should be some records.

    This SQL query can be used to get a count of these records.

     

    DECLARE @keeplogs as INT;
    DECLARE @past as varchar(8);
    -- Read the number of days to keep the log files
    set @keeplogs = (select strValue from tblSettings where strsettingLC = 'keeplogs')
    -- Calculate the date in the past in the form of yyyymmdd
    set @past = CONVERT(VARCHAR(8),DATEADD(DAY, [email protected], getutcdate()), 112)
    -- Query for all records smaller than past
    select Count(*) AS 'NR of records to be cleaned up' from tblLogs where LEFT(strDateTimeUTC, 8) < @past
    

     

     

     

    Delete records that should be cleaned up

     

    This SQL query can be used to clean up the obsolete records.

     

    DECLARE @keeplogs as INT;
    DECLARE @past as varchar(8);
    -- Read the number of days to keep the log files
    set @keeplogs = (select strValue from tblSettings where strsettingLC = 'keeplogs')
    -- Calculate the date in the past in the form of yyyymmdd
    set @past = CONVERT(VARCHAR(8),DATEADD(DAY, [email protected], getutcdate()), 112)
    -- Delete all records smaller than past
    delete from tblLogs where LEFT(strDateTimeUTC, 8) < @past 
    

     

     

     

    Delete all logging data

     

    For truncate:

    truncate table dbo.tbllogs

     

     

     

    Delete a specific type of records

     

    In most cases, a truncate will be used to delete i.e. 40GB of data. However, in some cases, not all logging should be deleted and in those scenario`s, a delete statement will be used.

    You can use the following delete statements for dbo.tbllogs in the Workspace Control database:

     

    Security Management: Applications:

    1. Check the amount of Applications log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 23

     

    2. Remove the Applications logging only from the database:

    delete from dbo.tblLogs where lngClassId = 23

     

    Security Management: Files and Folders:

    1. Check the amount of Files and Folders log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 24

     

    2. Remove the Files and Folders logging only from the database:

    delete from dbo.tblLogs where lngClassId = 24

     

    Security Management: Read-Only Blanketing:

    1. Check the amount of Read-Only Blanketing log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 25

     

    2. Remove the Read-Only Blanketing logging only from the database:

    delete from dbo.tblLogs where lngClassId = 25

     

    Security Management: Removable Disks:

    1. Check the amount of Removable Disks log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 26

     

    2. Remove the Removable Disks logging only from the database:

    delete from dbo.tblLogs where lngClassId = 26

     

    Security Management: IP Connections:

    1. Check the amount of IP Connections log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 54

     

    2. Remove the IP Connections logging only from the database:

    delete from dbo.tblLogs where lngClassId = 54

     

    Performance Management: Access Balancing:

    1. Check the amount of Access Balancing log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 28

     

    2. Remove the Access Balancing logging only from the database:

    delete from dbo.tblLogs where lngClassId = 28

     

    Performance Management: CPU Optimization:

    1. Check the amount of CPU Optimization log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 30

     

    2. Remove the CPU Optimization logging only from the database:

    delete from dbo.tblLogs where lngClassId = 30

     

    Performance Management: Memory Optimization:

    1. Check the amount of Memory Optimization log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 31

     

    2. Remove the Memory Optimization logging only from the database:

    delete from dbo.tblLogs where lngClassId = 31

     

    Performance Management: Instant Logoff:

    1. Check the amount of Instant Logoff log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 32

     

    2. Remove the Instant Logoff logging only from the database:

    delete from dbo.tblLogs where lngClassId = 32

     

    The Error log:

    1. Check the amount of Error log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 46

     

    2. Remove the Error logging only from the database:

    delete from dbo.tblLogs where lngClassId = 46

     

    Workspace Analysis: User Event Log:

    1. Check the amount of User Event Log entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 47

     

    2. Remove the User Event Logs only from the database:

    delete from dbo.tblLogs where lngClassId = 47

     

    User Settings Sampling mode data:

    1. Check the amount of User Settings Sampling mode entries via the select statement:

    select * from dbo.tblLogs where lngClassId = 48

     

    2. Remove the Error logging only from the database:

    delete from dbo.tblLogs where lngClassId = 48

     

    Action task entries in the User Event Log:

    1. Check the amount of Powerlaunch task entries in the User Event Log via the select statement:

    select * from dbo.tblLogs where lngClassId = 83

     

    2. Remove the Error logging only from the database:

    delete from dbo.tblLogs where lngClassId = 83

     

    NOTE: Perform a SHRINK of the Workspace Control database to cleanup the disk space.