Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inputs Required!! Expand / Collapse
Author
Message
Posted Sunday, January 27, 2013 9:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 29, 2013 9:13 PM
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.
Post #1412172
Posted Sunday, January 27, 2013 10:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1412179
Posted Sunday, January 27, 2013 10:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1412180
Posted Sunday, January 27, 2013 10:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 29, 2013 9:13 PM
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.
Post #1412183
Posted Sunday, January 27, 2013 11:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1412190
Posted Sunday, January 27, 2013 11:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1412194
Posted Monday, January 28, 2013 12:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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!

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
Post #1412200
Posted Monday, January 28, 2013 12:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
demonfox (1/28/2013)
[quote][b]
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
Guidelines for answers on Performance questions

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

Follow me @Twitter

Post #1412202
Posted Monday, January 28, 2013 12:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1412206
Posted Monday, January 28, 2013 12:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1412216
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse