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