Clarity

Expand all | Collapse all

System Logging of Config Changes

  • 1.  System Logging of Config Changes

    Posted 10-09-2015 01:57 PM

    Is there a way within Clarity to extract a log of which system admins made which config changes (similar to the results from attribute auditing)?



  • 2.  Re: System Logging of Config Changes

    Posted 10-13-2015 01:06 AM

    I don’t know of any log. Maybe some of the long time users of Clarity (sorry PPM) know how to get this information.

    Here’s a little SQL (MSSQL) that I threw together to shows which Admin has been mucking about in which tables.   It might be of some help.  You set the @DayAgo to the window of time you are interested in.

     

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @DayAgo INT
    SET @DayAgo = -7
    
    SET @SQL = '; DECLARE @Admins TABLE (ID INT); INSERT @Admins (ID) SELECT DISTINCT su.ID FROM niku.CMN_SEC_USER_GROUPS ug ' +  CHAR(10)
    + 'INNER JOIN niku.CMN_SEC_USERS su on su.ID = ug.USER_ID ' +  CHAR(10)
    + 'INNER JOIN niku.CMN_SEC_GROUPS sg on sg.ID = ug.GROUP_ID ' +  CHAR(10)
    + 'WHERE sg.GROUP_CODE LIKE ''%admin%'''  +  CHAR(10)
    SET @SQL = @SQL + '; DECLARE @TempTable TABLE (Updates INT, Update_By INT, TableName NVARCHAR(200)) ' +  CHAR(10)
    SELECT @SQL = @SQL + ';INSERT @TempTable (Updates, Update_By, TableName ) SELECT COUNT(*), LAST_UPDATED_BY, ''' + t.NAME + ''' FROM niku.'
    + t.NAME  + ' WHERE LAST_UPDATED_BY IN (SELECT ID FROM @Admins) AND LAST_UPDATED_DATE >= DATEADD(DAY, ' + CAST(@DayAgo as VARCHAR(10)) + ', GETDATE()) '
    + 'GROUP BY LAST_UPDATED_BY' + CHAR(10)
    FROM SYS.COLUMNS c
    INNER JOIN SYS.TABLES t ON c.OBJECT_ID = t.OBJECT_ID
    WHERE c.NAME LIKE 'LAST_UPDATED_BY'
    
    SET @SQL=@SQL+ '; SELECT u.FIRST_NAME + CHAR(32) + u.LAST_NAME AS NAME, t.UPDATES, t.TABLENAME, DATEADD(DAY, ' + CAST(@DayAgo as VARCHAR(10)) + ', GETDATE()) AS SINCE ' +  CHAR(10)
    + ' FROM @TempTable t INNER JOIN niku.CMN_SEC_USERS u ON u.ID = t.Update_By ' +  CHAR(10)
    + ' WHERE Updates > 0 ORDER BY  u.FIRST_NAME + CHAR(32) + u.LAST_NAME, t.UPDATES desc'
    EXEC (@SQL)
    

     

     

    It returns a results set that looks like this.:

     

     

    I guess it should only focus on configuration tables and not data tables but in my world, admin accounts shouldn’t even have access to data tables.

     

    V/r,

    Gene



  • 3.  Re: System Logging of Config Changes

    Posted 10-13-2015 09:10 AM

    Thanks!