Audit Modification

Here is an example of how to manipulate the audit modification tables to pull specific scenarios you want to monitor. The code below is specifically looking for a change in timekeepers for a specific type of multi-timekeeper where the effective date is prior to the current month.

SELECT
au.audate AS 'Date Changed'
,au.auuser AS 'Elite User'
,tklast+', '+tkfirst AS 'User Full Name'
,au.aukeyvalue1 AS 'Matter #'
,mdesc1 AS 'Matter Description'
,mopendt AS 'Open Date'
,au.auindex AS 'Audit Index'
,ad.audesc AS 'New Value'
,ap.audesc AS 'Previous Value'
,tkemail AS 'User Email'
FROM auditmod au
INNER JOIN auditmodd ad ON au.auindex=ad.auindex AND ad.autype = 'N' AND ad.audesc LIKE 'SOURCE%'
INNER JOIN auditmodd ap ON au.auindex=ap.auindex AND ap.autype = 'P' AND ap.audesc LIKE 'SOURCE%'
INNER JOIN matter ON aukeyvalue1=mmatter
INNER JOIN uaccess ON au.auuser=uaccess.uname
INNER JOIN timekeep ON uaccess.wvtkinit=timekeep.tkinit
WHERE autable = 'MTKPER'
AND audate >= GETDATE()-2
AND ad.audesc LIKE '%12/31/2025%'
AND mopendt < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
AND MONTH(au.audate) <> SUBSTRING(ad.audesc,CHARINDEX(' ',ad.audesc),CHARINDEX('/',ad.audesc,CHARINDEX(' ',ad.audesc)+1)-CHARINDEX(' ',ad.audesc))
AND LEFT(RIGHT(ap.audesc,CHARINDEX(' ',REVERSE(ap.audesc))+4),4)
 <> LEFT(RIGHT(ad.audesc,CHARINDEX(' ',REVERSE(ad.audesc))+4),4)
ORDER BY au.auindex