• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

SQL Triggers to Trim trailing spaces

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> SQL Triggers to Trim trailing spaces Page: [1]
Login
Message << Older Topic   Newer Topic >>
SQL Triggers to Trim trailing spaces - 27.Dec.2004 1:49:00 PM   
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)
Post #: 1
RE: SQL Triggers to Trim trailing spaces - 28.Dec.2004 3:51:00 AM   
Guest
The simpler one
-----------------
CREATE TRIGGER WebProxyLog_Insert
ON dbo.WebProxyLog
instead of INSERT
AS
insert [dbo].[WebProxyLog] (
ClientIP,
ClientUserName,
ClientAgent,
ClientAuthenticate,
logDate,
logTime,
service,
servername,
referredserver,
DestHost,
DestHostIP,
DestHostPort,
processingtime,
bytessent,
bytesrecvd,
protocol,
transport,
operation,
uri,
mimetype,
objectsource,
resultcode,
CacheInfo,
rule#1,
rule#2
)
select
RTRIM(ClientIP),
RTRIM(ClientUserName),
RTRIM(ClientAgent),
RTRIM(ClientAuthenticate),
logDate,
logTime,
RTRIM(service),
RTRIM(servername),
RTRIM(referredserver),
RTRIM(DestHost),
RTRIM(DestHostIP),
DestHostPort,
processingtime,
bytessent,
bytesrecvd,
RTRIM(protocol),
RTRIM(transport),
RTRIM(operation),
RTRIM(uri),
RTRIM(mimetype),
RTRIM(objectsource),
resultcode,
CacheInfo,
RTRIM(rule#1),
RTRIM(rule#2)
from inserted
-----------------------------

CREATE TRIGGER FirewallLog_INSERT
ON dbo.FirewallLog
instead of INSERT
AS
insert [dbo].[FirewallLog] (
ClientIP,
ClientUserName,
ClientAgent,
ClientAuthenticate,
logDate,
logTime,
service,
servername,
referredserver,
DestHost,
DestHostIP,
DestHostPort,
processingtime,
bytessent,
bytesrecvd,
protocol,
transport,
operation,
uri,
mimetype,
objectsource,
resultcode,
CacheInfo,
rule#1,
rule#2,
sessionid,
connectionid
)
select
RTRIM(ClientIP),
RTRIM(ClientUserName),
RTRIM(ClientAgent),
RTRIM(ClientAuthenticate),
logDate,
logTime,
RTRIM(service),
RTRIM(servername),
RTRIM(referredserver),
RTRIM(DestHost),
RTRIM(DestHostIP),
DestHostPort,
processingtime,
bytessent,
bytesrecvd,
RTRIM(protocol),
RTRIM(transport),
RTRIM(operation),
RTRIM(uri),
RTRIM(mimetype),
RTRIM(objectsource),
resultcode,
CacheInfo,
RTRIM(rule#1),
RTRIM(rule#2),
sessionid,
connectionid
from inserted

(in reply to dmatos)
  Post #: 2
RE: SQL Triggers to Trim trailing spaces - 28.Dec.2004 3:53:00 AM   
Guest
The triggers above are for ISA2000, but you can slightly change they to adapt for ISA2004.

(in reply to dmatos)
  Post #: 3
RE: SQL Triggers to Trim trailing spaces - 28.Dec.2004 3:14:00 PM   
dmatos

 

Posts: 6
Joined: 17.Oct.2002
From: Portugal
Status: offline
Of course... stupid me... [Smile]

Here goes the revised ISA2004 Version:

-------------------
code:
 
CREATE TRIGGER trInsertWebProxy ON [dbo].[WebProxyLog]
INSTEAD OF INSERT
AS

INSERT [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])

SELECT
RTRIM(ClientIP),
RTRIM(ClientUserName),
RTRIM(ClientAgent),
RTRIM(ClientAuthenticate),
logDate,
logTime,
RTRIM(service),
RTRIM(servername),
RTRIM(referredserver),
RTRIM(DestHost),
RTRIM(DestHostIP),
DestHostPort,
processingtime,
bytesrecvd,
bytessent,
RTRIM(protocol),
RTRIM(transport),
RTRIM(operation),
RTRIM(uri),
RTRIM(mimetype),
RTRIM(objectsource),
resultcode,
CacheInfo,
RTRIM([rule]),
RTRIM(FilterInfo),
RTRIM(SrcNetwork),
RTRIM(DstNetwork),
ErrorInfo,
RTRIM([Action])
FROM inserted


-------------------


CREATE TRIGGER trInsertFirewall ON [dbo].[FirewallLog]
INSTEAD OF INSERT
AS

INSERT [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)

SELECT
RTRIM(servername),
logDate,
logTime,
RTRIM(protocol),
RTRIM(Source),
RTRIM(Destination),
RTRIM(OriginalClientIP),
RTRIM(SourceNetwork),
RTRIM(DestinationNetwork),
RTRIM([Action]),
resultcode,
RTRIM([rule]),
RTRIM(ApplicationProtocol),
RTRIM(Bidirectional),
bytessent,
bytessentDelta,
bytesrecvd,
bytesrecvdDelta,
connectiontime,
connectiontimeDelta,
RTRIM(SourceProxy),
RTRIM(DestinationProxy),
RTRIM(SourceName),
RTRIM(DestinationName),
RTRIM(ClientUserName),
RTRIM(ClientAgent),
sessionid,
connectionid,
RTRIM(Interface),
RTRIM(IPHeader),
RTRIM(Payload)

FROM inserted





(in reply to dmatos)
Post #: 4

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> SQL Triggers to Trim trailing spaces Page: [1]
Jump to:

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
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts