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 questions

    Link 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).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:

  • 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 questions

    Link 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 questions

    Link 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 questions

    Link 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 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_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 questions

    Link 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 questions

    Link 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 questions

    Link 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 questions

    Link 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