How to Loop Through Audit Records

Recently I needed a way to essentially ‘loop’ through all the records in our NetTrax.NET Audit History for the given record and return back the one that is that entries ‘check in’…

So, what I did is I create a view that UNIONs all the Audit tables together (FoldersAudit, FoldersAudit_EmpComp,  FoldersAudit_HR, DocumentsAudit, DocumentsAudit_EmpComp, and DocumentsAudit_HR)

SELECT     *
FROM         tbl_FoldersAudit

UNION ALL

SELECT     *
FROM         tbl_FoldersAudit_EmpComp

UNION ALL

SELECT     *
FROM         tbl_FoldersAudit_HR

UNION ALL

SELECT     *
FROM         tbl_DocumentsAudit

UNION ALL

SELECT     *
FROM         tbl_DocumentsAudit_EmpComp

UNION ALL

SELECT     *
FROM         tbl_DocumentsAudit_HR

 

That guy returns almost 3 million records so I wouldn’t suggest running it as SELECT * FROM…

So, now that I have ALL my audit entries into one nice view, then I wrote my function… I pass in the rows Auditing_ID and Audit_TS then I evaluate the top 1 record where the date is greater than the Auditing_TS I passed in. There is some other logic as far as checkin / checkout type but you’ll see that below in the actual function

 

[dbo].[fn_CalculateCheckInDate]
(
                -- Add the parameters for the function here
                @AuditingID VARCHAR(38),
                @AuditTS DATETIME
)
RETURNS DATETIME
AS
BEGIN

                DECLARE @Return AS DATETIME

                --Go get the first record from the AuditUnion view where the Audit_TS is greater than
                --the Audit_TS of the record passed in and the AuditMetricID is a 'check in' or 'check out'.
                --The next (first) record after this one by date would be the next transfer.
                SET @Return =  (              SELECT TOP 1 Audit_TS
                                                                FROM vw_AuditUnion
                                                                WHERE Audit_TS > @AuditTS
                                                                AND Auditing_ID = @AuditingID
                                                                AND AuditMetricID IN (8,9)
                                                                ORDER BY Audit_TS
                                                )

                Return @Return

END

 

Now, I can call the function as a column in the select view (for my report) and get the timestamp of the check in. I’m passing in a hardcoded Auditing_ID for testing purposes right now but obviously this will be different criteria when I run the report (it will be a date range). I’m saying WHERE AuditMetricID IN (9) because in the report I only want rows for check out’s nothing else.

SELECT Audit_TS, dbo.fn_CalculateCheckInDate(Auditing_ID, Audit_TS) AS 'CheckIn_TS'
FROM tbl_FoldersAudit FA
WHERE Auditing_ID = '83ba4bae-9a8d-4bd9-958f-81355ad80fe7'
AND AuditMetricID IN (9)
ORDER BY Audit_TS

Here is a side-by-side of the NetTrax Audit History and my SQL Results

You’ll notice that my “WhoCares” line isn’t included because in my select, and in my function I’m only looking at Check In / Check Out so I won’t erroneously get the audit row for an edit or a mass update for example!

If the record has been checked out, but not back in, the CheckIn_TS will be NULL