SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inputs Required!!


Inputs Required!!

Author
Message
sainatth.wagh
sainatth.wagh
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 68
Hello,

I want to implement the audit trail on the daily transactions that are being performed.
Please share the thoughts on how can i implement this functionality.
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1599
If you are looking to audit CURD operations on tables. Like if you want to know which columns have been updated and want to know the old values as well, an easy way is to implement Change Data Capture. Here is the link http://msdn.microsoft.com/en-in/library/bb522489(v=sql.105).aspx

In case you have a different requirement. Please do specify.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1539 Visits: 1192
Lokesh Vij (1/27/2013)
If you are looking to audit CURD operations on tables. Like if you want to know which columns have been updated and want to know the old values as well, an easy way is to implement Change Data Capture. Here is the link http://msdn.microsoft.com/en-in/library/bb522489(v=sql.105).aspx

In case you have a different requirement. Please do specify.

CDC is more like versioning than audit ; what does it mean by audit trail on daioy transactions ?
does it more related to logging of transaction by a login or logging of data ?

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
sainatth.wagh
sainatth.wagh
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 68
Hi,

Yes by trail i mean the login and logoff feature.
Any inputs on that would be helpful. Regarding the CDC functionality, I have already implemented that.
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1599
sainatth.wagh (1/27/2013)
Hi,

Yes by trail i mean the login and logoff feature.
Any inputs on that would be helpful. Regarding the CDC functionality, I have already implemented that.


If you are talking about login/logoff then you need to implement custom auditing on the server. I don not think there is any direct way to capture such details.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1599
demonfox (1/27/2013)
[quote]
CDC is more like versioning than audit ; what does it mean by audit trail on daioy transactions ?
does it more related to logging of transaction by a login or logging of data ?


Hey Demonfox, thing for bringing these questions :-)

Just to clarify CDC is more of a field level auditing then versioning!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1539 Visits: 1192
Lokesh Vij (1/27/2013)
demonfox (1/27/2013)
[quote]
CDC is more like versioning than audit ; what does it mean by audit trail on daioy transactions ?
does it more related to logging of transaction by a login or logging of data ?


Hey Demonfox, thing for bringing these questions :-)

Just to clarify CDC is more of a field level auditing then versioning!

Hehe yes , it is ..
when I hear the word audit , I think of security :-P
and with CDC , Change data Capture , I think of it as versioning or logging ; techincal term may be Field Level Auditing ; will keep in mind ... :-D

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1599
demonfox (1/28/2013)
[quote][b]
Hehe yes , it is ..
when I hear the word audit , I think of security :-P
and with CDC , Change data Capture , I think of it as versioning or logging ; techincal term may be Field Level Auditing ; will keep in mind ... :-D


Yes..other thing worth taking note of is that when we talk about versioning, we should be able to go back and track the version number and changes made. CDC does not retain version numbers, though it retains LSN. Furthermore, Change Tables used to capture changes are cleaned-up at regular intervals thru jobs. Hence, old changes recorded (old versions in your term), shall be cleaned-up!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1599
OP here is how I have set-up custom auditing on the Server. The script given here will create an Audit Table and try to record DB access information. I use this to track when was the last time any user has accessed the DB and if the DB is not accessed for more than 15 days, I shall take that DB offline.

USE master
GO

-- ----------------------------------------------
-- Create AuidtTable if it does not exist
-- ----------------------------------------------

IF Object_id('AuditTable') IS NULL
CREATE TABLE AuditTable
(
DatabaseName VARCHAR(500),
last_access_time DATETIME,
no_of_days INT,
last_upd_dtm DATETIME
);
GO

-- ----------------------------------------------
-- Insert entries from all DB's.
-- ----------------------------------------------

INSERT master..AuditTable
SELECT name,
Getdate(),
0,
Getdate()
FROM sys.databases
WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' ) ;
GO
-- ----------------------------------------------
-- Update audit table for the first time
-- ----------------------------------------------

WITH
Base
AS (SELECT Db_name(database_id) DatabaseName,
last_user_seek AS last_access_time
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_scan
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_lookup
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_update
FROM sys.dm_db_index_usage_stats),
AccessInfo
AS (SELECT DatabaseName,
Max(last_access_time) last_access_time,
Datediff(dd, Max(last_access_time), Getdate()) AS no_of_days
FROM Base
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb'))
MERGE master..AuditTable AS tgt
USING (SELECT DatabaseName,last_access_time,no_of_days,getdate() last_upd_dtm FROM AccessInfo) AS src
ON tgt.databasename = src.databasename
WHEN MATCHED
THEN
UPDATE SET tgt.last_access_time = src.last_access_time, tgt.no_of_days = src.no_of_days, tgt.last_upd_dtm = src.last_upd_dtm
WHEN NOT MATCHED BY TARGET
THEN
INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm)
VALUES ( src.DatabaseName,src.last_access_time,src.no_of_days,src.last_upd_dtm);
GO



Hope you can modify this in your own way.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
Its a nice script, I have a question wont scheduled Index Maintenance plans have the effect of showing up in the last_user_update?

You could also change the nested CTE to simply be


Select db_name(database_id),Max(x.Last_Date) Last_Access_time
From
sys.dm_db_index_usage_stats
CROSS APPLY(VALUES(last_user_update),(last_user_lookup),(last_user_scan),(last_user_seek)) x (last_date)
Where last_date is NOT NULL
AND db_name(database_id) not in ('msdb','master','tempdb')
group by database_id



Drop that into a single CTE for use as the source for the merge and you should be good to go, all you then have to do is the date diff on the merge.

I would also look at adding a clause to the WHEN MATCHED to only update those rows where the Last_Access_Time is different

So the code reads



;With Cte_Src(
Select
db_name(database_id) databasename
,Max(x.Last_Date) Last_Access_time
,GETDATE() curr_time
From
sys.dm_db_index_usage_stats
CROSS APPLY(VALUES(last_user_update)
,(last_user_lookup)
,(last_user_scan)
,(last_user_seek)) x (last_date)
Where last_date is NOT NULL
AND db_name(database_id) not in ('msdb','master','tempdb')
group by database_id
)
MERGE master..AuditTable AS tgt
USING Cte_Src AS src
ON tgt.databasename = src.databasename
WHEN MATCHED AND src.Last_Access_time != tgt.Last_Access_time
THEN
UPDATE SET
tgt.last_access_time = src.last_access_time
, tgt.no_of_days = DateDiff(d,src.last_access_time, src.curr_time)
, tgt.last_upd_dtm = src.curr_time
WHEN NOT MATCHED BY TARGET
THEN
INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm)
VALUES ( src.DatabaseName,src.last_access_time,DateDiff(d,src.last_access_time, src.curr_time));



It needs testing.

One last thing user objects should not be in the Master DB, I would create a centralised Audit Database or a specialised DBA database on each server to hold this type of thing as all you are doing is clutering up the Master DB.

(code reformated)

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search