Inputs Required!!
-
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.
-
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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).aspxIn 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: -
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.
-
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
demonfox (1/27/2013)
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (1/27/2013)
demonfox (1/27/2013)
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 😛
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 ... 😀
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
demonfox (1/28/2013)
:hehe: yes , it is ..
when I hear the word audit , I think of security 😛
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 ... 😀
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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 masterGO
-- ----------------------------------------------
-- 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_dtmDATETIME
);
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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 -
Hey Jason, thanks for your valuable inputs. I shall have these incorporated and tested soon.
Thanks 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
No problem, let me know how it turns out.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (1/28/2013)
No problem, let me know how it turns out.Cool! This works well. Here is the modified (few compilation issues rectified) and tested version.
;WITH Cte_Src AS (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),GETDATE());
Thanks!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks people, with the similar piece of code it has solved my probelm....:-):-):-)
-
sainatth.wagh (1/28/2013)
Thanks people, with the similar piece of code it has solved my probelm....:-):-):-)Wonderful! Glad to know that.
All glories to Jason as well for optimizing my code 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply