locked
help with this logon audit RRS feed

  • Question

  • Hi,

     

    i need to make an audit about sql logins.the problem that i have,is that our company uses an ERP that called Priority.when a user first login to the Priority it login with his SQL Server Login and he get an SPID.

    every time that he open any window in his program,the program make again login with his username and password that he entered in the first time and then he get a new SPID.

    i need to audit only the first login to the SQL Server(loginame ,hostname,date login,).

    so i write this login audit trigger that check if the username and his hostname not exists in the sys.sysprocesses

    then write to the table the info.

    the problem with my script is when i catch the hostname i catch it with his IP address not his Netbios name like in the hostname in the sys.sysprocesses,that's why i can't check the if statement against the hostname in the sys.sysprocesses.how i can fix this problem with the IP address instead of Netbios name?

    or maybe you know a better way to achieve what i need.

    THX

    --THE TRIGGER

    create trigger ServerWideLoginLogs
    on all server
    with execute as self
    for LOGON
    as
    begin
    Declare @data xml
    Declare @LoginName varchar (50)
    Declare @ClientHost varchar (50)
    set @data=Eventdata()
    set @LoginName =  @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)')
    set @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(50)')

    if not exists (select ltrim(rtrim(loginame)),ltrim(rtrim(hostname)) from sys.sysprocesses where spid > 50
    and loginame = @LoginName and hostname = @ClientHost)

    Insert into system.dbo.logs_audit values
    (
    @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(200)'),
    getdate(),
    @data.value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(8)'),
    @data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(200)'),
    @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(200)'))

    end
    Go

    Saturday, July 12, 2008 4:04 PM

Answers

  • Avi,

     

    Instead of using the /EVENT_INSTANCE/ClientHost, try using the HOST_NAME() function and match the output of that to the hostname in sysprocesses.

     

    Saturday, July 12, 2008 4:17 PM

All replies

  • Avi,

     

    Instead of using the /EVENT_INSTANCE/ClientHost, try using the HOST_NAME() function and match the output of that to the hostname in sysprocesses.

     

    Saturday, July 12, 2008 4:17 PM
  • Thx.it's works good.

    by the way why does the /EVENT_INSTANCE/ClientHost = IP ADDRESS ?

    is it by design?

    THX

    Saturday, July 12, 2008 4:57 PM
  • The BOL for the Logon Triggers says that it will only return the Local Hosts name, otherwise it is the IP address.  I have no idea why it was coded that way, perhaps because the netbios name is not always guaranteed, but the IP address is?  

    Saturday, July 12, 2008 5:01 PM