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

Audit Modification Trails

With so many fields available in the client, matter and timekeep master files, changes are made to these records on a consistent basis. Often it is helpful to be able to see who changed what and when that change occurred. There are two tables within Elite that can help us in this search: the auditmod and auditmodd tables. The first contains the summary info for the changes and the second contains the details and descriptions. The code below will pull any changes to information in the matter master fields of matter contact, matter address and matter billing attorney that was performed yesterday by any user other than abc and def. Obviously, those can be tweaked to your individual needs. The code will return 2 lines of information for each change. The first will be the 'P' row which indicates the previous information. The second will be the 'N' row, which indicates the new information. This query also utilizes the Matter Description user defined function and the Timekeeper Full Name user defined function.

Code

SELECT 
audate AS 'Date'
,autime AS 'Time'
,auuser AS 'User'
,mmatter AS 'Matter #'
,clname1 AS 'Client Name'
,dbo.udfMattDesc(mmatter) AS 'Matter Description'
,dbo.udfTKFullName(mbillaty) AS 'Billing Aty'
,autype AS 'P/N'
,audesc AS 'Audit Description'
FROM auditmod
INNER JOIN auditmodd ON auditmod.auindex = auditmodd.auindex
INNER JOIN matter ON aukeyvalue1 = mmatter
INNER JOIN client ON mclient=clnum
WHERE autable = 'matter'
AND audate >= DATEADD(dd,0,DATEDIFF(dd,1,GETDATE()))
AND auuser NOT IN ('abc','def')
AND (
audesc LIKE 'mcontact%'
OR
audesc LIKE 'maddr%'
OR
audesc LIKE 'mbillaty%'
)
ORDER BY audate,aukeyvalue1,auditmodd.auindex

Results

Date	Time	User	Matter #	Client Name	Matter Description	Billing Aty	P/N	Audit Description
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	mbillaty 7381
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	mcontact
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr1
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr2
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr3
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	mbillaty 1234
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	mcontact Jim McMahon
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr1 Global Industries
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr2 123 Wellington Court
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr3 Lincolnshire, IL