locked
Get Audit log Info RRS feed

  • Question

  • I am building an Audit trail using Triggers and I want to store the below columns in it, can you anyone let me know how to get the below details:

    • Transaction ID
    • IP address
    • MAC address

    -Shreepal

    Wednesday, September 21, 2016 5:15 AM

Answers

  • Of course my query would give many rows you run it without any where condition. Now that you have clarified your requirement below should help

    CREATE TRIGGER Trig_ShreeTest ON TestTable
    AFTER UPDATE, INSERT
    AS
    BEGIN
    declare @TransactionID smallint , @IPAddress varchar(48), @MacAddress nvarchar(50)
    
    select @TransactionID=ec.session_id, @MacAddress=substring(sp.net_address,1,2)+'-'+substring(sp.net_address,3,2)+'-'+substring(sp.net_address,5,2)
    +'-'+substring(sp.net_address,7,2)+'-'+substring(sp.net_address,9,2)+'-'+substring(sp.net_address,11,2),
    @IPAddress=ec.client_net_address
    from sys.dm_exec_connections ec
    inner join sys.sysprocesses sp
    on ec.session_id=sp.spid
    where ec.session_id=@@SPID
      INSERT INTO TestTableAudit 
      (Id, Name, Test1, Test2, AuditUser, AuditDate, TransactionId, IPAddress, MACAddress)
      SELECT I.ID, I.Name, I.Test1, I.Test2, SUSER_SNAME(), GETDATE(), @TransactionID, @IPAddress,@MacAddress
      FROM  TestTable T 
      INNER JOIN INSERTED I ON T.ID=I.ID
    END
    GO


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 21, 2016 10:37 AM
    Answerer

All replies

  • I am building an Audit trail using Triggers and I want to store the below columns in it, can you anyone let me know how to get the below details:

    • Transaction ID
    • IP address
    • MAC address

    -Shreepal

    which environment you are looking for in SQL server,sharepoint or etc?

    Regards, S_NO "_"

    Wednesday, September 21, 2016 5:42 AM
  • Transaction ID can be retrieved using the system function @@SPID

    IP Address can be found in sys.dm_exec_connections

    There is no way that I am aware of to get the MAC Address.

    However, these could be obtained far more efficiently in an Extended Event payload.


    Martin Cairney SQL Server MVP

    Wednesday, September 21, 2016 6:18 AM
  • Hi Shreepal,

    Below should give you what you require BUT the mac address you are getting is for the client not for the server.

    select ec.session_id,sp.hostname, MAC=substring(sp.net_address,1,2)+'-'+substring(sp.net_address,3,2)+'-'+substring(sp.net_address,5,2)
    +'-'+substring(sp.net_address,7,2)+'-'+substring(sp.net_address,9,2)+'-'+substring(sp.net_address,11,2),
    ec.client_net_address,ec.local_net_address
    from sys.dm_exec_connections ec
    inner join sys.sysprocesses sp
    on ec.session_id=sp.spid

    For example if you RDP on machine and run query using SSMS which is on different machine it will give you MAC ADDRESS of machine on which SSMS is present not your machine. The query to obtain mac address is shamelessly copied from SQLServerCentral.

    This Stackoverflow Liink has answer by Martin Smith you can also take that query to get mac address.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, September 21, 2016 7:00 AM
    Answerer
  • Thanks Shashank for the response and the query, but when I run the query it gives me 100+ results. 

    Now, let me give you the scenario where I need to use this info:

    CREATE TRIGGER Trig_ShreeTest ON TestTable
    AFTER UPDATE, INSERT
    AS
    BEGIN
      INSERT INTO TestTableAudit 
      (Id, Name, Test1, Test2, AuditUser, AuditDate, TransactionId, IPAddress, MACAddress)
      SELECT I.ID, I.Name, I.Test1, I.Test2, SUSER_SNAME(), GETDATE(), <Need TransactionId>, <Need IPAddress>, <Need MACAddress>
      FROM  TestTable T 
      INNER JOIN INSERTED I ON T.ID=I.ID
    END
    GO

    How can I just get the required info for above? Thanks again!

    -Shreepal

    Wednesday, September 21, 2016 9:19 AM
  • Of course my query would give many rows you run it without any where condition. Now that you have clarified your requirement below should help

    CREATE TRIGGER Trig_ShreeTest ON TestTable
    AFTER UPDATE, INSERT
    AS
    BEGIN
    declare @TransactionID smallint , @IPAddress varchar(48), @MacAddress nvarchar(50)
    
    select @TransactionID=ec.session_id, @MacAddress=substring(sp.net_address,1,2)+'-'+substring(sp.net_address,3,2)+'-'+substring(sp.net_address,5,2)
    +'-'+substring(sp.net_address,7,2)+'-'+substring(sp.net_address,9,2)+'-'+substring(sp.net_address,11,2),
    @IPAddress=ec.client_net_address
    from sys.dm_exec_connections ec
    inner join sys.sysprocesses sp
    on ec.session_id=sp.spid
    where ec.session_id=@@SPID
      INSERT INTO TestTableAudit 
      (Id, Name, Test1, Test2, AuditUser, AuditDate, TransactionId, IPAddress, MACAddress)
      SELECT I.ID, I.Name, I.Test1, I.Test2, SUSER_SNAME(), GETDATE(), @TransactionID, @IPAddress,@MacAddress
      FROM  TestTable T 
      INNER JOIN INSERTED I ON T.ID=I.ID
    END
    GO


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 21, 2016 10:37 AM
    Answerer