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