0

I'm trying to capture logins time with a trigger using EVENTDATA() function. But It's giving Null values only for all types of events. Can anyone please help me on this? This is my Trigger

CREATE TRIGGER trgBlockIP
    ON ALL SERVER
    FOR LOGON
    AS
    BEGIN
       DECLARE @clientip NVARCHAR(15);
       SET @clientip = (
                          SELECT
                             EVENTDATA().value(
                                                 '(/EVENT_INSTANCE/Clienthost)[1]',
                                                 'NVARCHAR(15)'
                                              )
                       );
       IF EXISTS (
                    SELECT ip
                    FROM   master.dbo.BLockedIPAddresses
                    WHERE  ip = @clientip
                 )
       BEGIN
          PRINT 'Your IP Address is blocked';
          ROLLBACK;
       END;
       ELSE
       BEGIN
          DECLARE @Range VARCHAR(15);
          SELECT
             @Range = SUBSTRING(
                                  @clientip,
                                  1,
                                  LEN(@clientip)
                                  - CHARINDEX('.', REVERSE(@clientip))
                               ) + '.*';
          IF EXISTS (
                       SELECT ip
                       FROM   master.dbo.BLockedIPAddresses
                       WHERE  ip = @Range
                    )
          BEGIN
             PRINT 'Your IP Address Range is blocked';
             ROLLBACK;
          END;
       END;
    END;
    GO

1 Answer 1

5

XML is case sensitive, and you're not capitalizing things properly.

"Clienthost" should be "ClientHost", which you can see in the documented schema: <xs:element name="ClientHost" type="SSWNAMEType"/>

Additionally, the element type SSWNAMEType has a maximum length of 128 bytes, not 15 bytes.

<!--  SSWNAMEType  -->
<xs:simpleType name="SSWNAMEType">
<xs:restriction base="xs:string">
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.