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