dmatos
Posts: 6
Joined: 17.Oct.2002
From: Portugal
Status: offline
|
Hello.
I made two SQL 'INSTEAD OF' Triggers to trim trailing white spaces from ISA SQL Logs.
I am sure that there are more efficient ways to accomplish this, but I don't have much experience in T-SQL Programming, so this is what I got.
Feel free to use it and/or suggest changes!
(Just run these in query analyzer to create the triggers that modify the contents of the record before they are inserted)
--------------FirewallLog-----------
CREATE TRIGGER trInsertFirewall ON [dbo].[FirewallLog]
INSTEAD OF INSERT
AS
DECLARE @servername as nvarchar(128),
@logDate as datetime,
@logTime as datetime,
@protocol as varchar(32),
@Source as varchar(32),
@Destination as varchar(32),
@OriginalClientIP as varchar(32),
@SourceNetwork as nvarchar(128),
@DestinationNetwork as nvarchar(128),
@Action as varchar(32),
@resultcode as int,
@rule as nvarchar(128),
@ApplicationProtocol as nvarchar(128),
@Bidirectional as varchar(5),
@bytessent as float,
@bytessentDelta as float,
@bytesrecvd as float,
@bytesrecvdDelta as float,
@connectiontime as int,
@connectiontimeDelta as int,
@SourceProxy as varchar(32),
@DestinationProxy as varchar(32),
@SourceName as varchar(255),
@DestinationName as varchar(255),
@ClientUserName as varchar(514),
@ClientAgent as varchar(255),
@sessionid as int,
@connectionid as int,
@Interface as varchar(25),
@IPHeader as varchar(255),
@Payload as varchar(255)
SELECT
@servername = RTRIM(servername),
@logDate = logDate,
@logTime = logTime,
@protocol = RTRIM(protocol),
@Source = RTRIM(Source),
@Destination = RTRIM(Destination),
@OriginalClientIP = RTRIM(OriginalClientIP),
@SourceNetwork = RTRIM(SourceNetwork),
@DestinationNetwork = RTRIM(DestinationNetwork),
@Action = RTRIM([Action]),
@resultcode = resultcode,
@rule = RTRIM([rule]),
@ApplicationProtocol = RTRIM(ApplicationProtocol),
@Bidirectional = RTRIM(Bidirectional),
@bytessent = bytessent,
@bytessentDelta = bytessentDelta,
@bytesrecvd = bytesrecvd,
@bytesrecvdDelta = bytesrecvdDelta,
@connectiontime = connectiontime,
@connectiontimeDelta = connectiontimeDelta,
@SourceProxy = RTRIM(SourceProxy),
@DestinationProxy = RTRIM(DestinationProxy),
@SourceName = RTRIM(SourceName),
@DestinationName = RTRIM(DestinationName),
@ClientUserName = RTRIM(ClientUserName),
@ClientAgent = RTRIM(ClientAgent),
@sessionid = sessionid,
@connectionid = connectionid,
@Interface = RTRIM(Interface),
@IPHeader = RTRIM(IPHeader),
@Payload = RTRIM(Payload)
FROM inserted
insert INTO [dbo].[FirewallLog]
(servername, logDate, logTime, protocol, Source, Destination, OriginalClientIP,
SourceNetwork, DestinationNetwork, [Action], resultcode, [rule], ApplicationProtocol,
Bidirectional, bytessent, bytessentDelta, bytesrecvd, bytesrecvdDelta, connectiontime,
connectiontimeDelta, SourceProxy, DestinationProxy, SourceName, DestinationName, ClientUserName,
ClientAgent, sessionid, connectionid, Interface, IPHeader, Payload)
VALUES
(@servername, @logDate, @logTime, @protocol, @Source, @Destination, @OriginalClientIP,
@SourceNetwork, @DestinationNetwork, @Action, @resultcode, @rule, @ApplicationProtocol,
@Bidirectional, @bytessent, @bytessentDelta, @bytesrecvd, @bytesrecvdDelta, @connectiontime,
@connectiontimeDelta, @SourceProxy, @DestinationProxy, @SourceName, @DestinationName, @ClientUserName,
@ClientAgent, @sessionid, @connectionid, @Interface, @IPHeader, @Payload)
----------------WebProxyLog-----------
CREATE TRIGGER trInsertWebProxy ON [dbo].[WebProxyLog]
INSTEAD OF INSERT
AS
DECLARE @ClientIP as varchar(32),
@ClientUserName as nvarchar(514),
@ClientAgent as varchar(128),
@ClientAuthenticate as varchar(5),
@logDate as datetime,
@logTime as datetime,
@service as varchar(16),
@servername as nvarchar(32),
@referredserver as varchar(32),
@DestHost as varchar(255),
@DestHostIP as varchar(32),
@DestHostPort as int,
@processingtime as int,
@bytesrecvd as int,
@bytessent as int,
@protocol as varchar(12),
@transport as varchar(8),
@operation as varchar(24),
@uri as varchar(2048),
@mimetype as varchar(32),
@objectsource as varchar(12),
@resultcode as int,
@CacheInfo as int,
@rule as nvarchar(128),
@FilterInfo as nvarchar(128),
@SrcNetwork as nvarchar(128),
@DstNetwork as nvarchar(128),
@ErrorInfo as int,
@Action as varchar(32)
SELECT
@ClientIP = RTRIM(ClientIP),
@ClientUserName = RTRIM(ClientUserName),
@ClientAgent = RTRIM(ClientAgent),
@ClientAuthenticate = RTRIM(ClientAuthenticate),
@logDate = logDate,
@logTime = logTime,
@service = RTRIM(service),
@servername = RTRIM(servername),
@referredserver = RTRIM(referredserver),
@DestHost = RTRIM(DestHost),
@DestHostIP = RTRIM(DestHostIP),
@DestHostPort = DestHostPort,
@processingtime = processingtime,
@bytesrecvd = bytesrecvd,
@bytessent = bytessent,
@protocol = RTRIM(protocol),
@transport = RTRIM(transport),
@operation = RTRIM(operation),
@uri = RTRIM(uri),
@mimetype = RTRIM(mimetype),
@objectsource = RTRIM(objectsource),
@resultcode = resultcode,
@CacheInfo = CacheInfo,
@rule = RTRIM([rule]),
@FilterInfo = RTRIM(FilterInfo),
@SrcNetwork = RTRIM(SrcNetwork),
@DstNetwork = RTRIM(DstNetwork),
@ErrorInfo = ErrorInfo,
@Action = RTRIM([Action])
FROM inserted
insert INTO [dbo].[WebProxyLog]
(ClientIP, ClientUserName, ClientAgent, ClientAuthenticate, logDate, logTime,
service, servername, referredserver, DestHost, DestHostIP, DestHostPort,
processingtime, bytesrecvd, bytessent, protocol, transport, operation, uri, mimetype,
objectsource, resultcode, CacheInfo, [rule], FilterInfo, SrcNetwork, DstNetwork,
ErrorInfo, [Action])
VALUES
(@ClientIP, @ClientUserName, @ClientAgent, @ClientAuthenticate, @logDate, @logTime,
@service, @servername, @referredserver, @DestHost, @DestHostIP, @DestHostPort,
@processingtime, @bytesrecvd, @bytessent, @protocol, @transport, @operation, @uri, @mimetype,
@objectsource, @resultcode, @CacheInfo, @rule, @FilterInfo, @SrcNetwork, @DstNetwork,
@ErrorInfo, @Action)
|
|
My Subscription |
My Forums |
Address Book |
Member List |
Search |
FAQ |
Ticket List |
Log Out
Printable Version
SQL Triggers to Trim trailing spaces - 
![[Smile]](/image/smiles/smile.gif)
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread