RE: SQL Logging
- From: "David V. Dellanno" <ddellanno@xxxxxxxxxx>
- To: "[ISAserver.org Discussion List]" <isalist@xxxxxxxxxxxxx>
- Date: Mon, 2 Jun 2003 04:42:41 -0400
Ditto!
-----Original Message-----
From: William Robertson [mailto:robertson.william@xxxxxxxxxxxxxx]
Sent: Monday, June 02, 2003 3:11 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging
http://www.ISAserver.org
Hi Jim/Cismic
Any word on the SQL Logging...? I am dead keen to get my grubby little
paws on that code of yours Cismic, if you'll let me :)
Cheers
William R.
-----Original Message-----
From: Jim Harrison [mailto:jim@xxxxxxxxxxxx]
Sent: 30 May 2003 20:02 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging
http://www.ISAserver.org
We've been patiently awaiting those wondrous tools of yours, Joseph.
Please zip them up and mail them to me, or I can give you an FTP folder
if they're really huge.
Jim Harrison
MCP(NT4, W2K), A+, Network+, PCG http://www.microsoft.com/isaserver
http://isaserver.org/Jim_Harrison
http://isatools.org
Read the help, books and articles!
----- Original Message -----
From: "cismic" <cismic@xxxxxxx>
To: "[ISAserver.org Discussion List]" <isalist@xxxxxxxxxxxxx>
Sent: Friday, May 30, 2003 09:39
Subject: [isalist] RE: SQL Logging
http://www.ISAserver.org
Hi All,
I've been working many 90 hour weeks since last Oct and all of those
basically away from my home. So, I've not been able to concentrate on
getting my listings to Jim Harrison.
I process for all Web proxy, Firewall, and packet filters from the ISA
logs. I have another script that moves the logs To another location
nightly. I do this so I can quickly move the logs to my machine(s) that
process log information.
If you want the items zipped up please let me know and I'll be happy to
get you all the table(s), stored procedures for SQL2000, The scripts
that move the logs, and the scripts that read the logs and calls the
stored procedures. Other wise I'll post the items seperately here.
Joseph
The tables look like the following:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DbWC3ISAFIRLog]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DbWC3ISAFIRLog]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DbWC3ISAPACLog]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DbWC3ISAPACLog]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DbWC3ISAWEBLog ]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DbWC3ISAWEBLog ]
GO
CREATE TABLE [dbo].[DbWC3ISAFIRLog] (
[ReportingMachine] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [LogFileName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [c_IP] [nvarchar] (18) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [cs_UserName] [nvarchar] (25)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [c_Agent] [nvarchar] (255)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [sc_Authenticated] [nchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AccessDate] [nvarchar]
(8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AccessTime] [nvarchar]
(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [s_SvcName]
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_ComputerName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_Referred] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_Host] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_IP] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_Port] [int] NULL , [Time_Taken]
[int] NULL , [cs_Bytes] [int] NULL , [sc_Bytes] [int] NULL ,
[cs_Protocol] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [cs_Transport] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [s_Opertion] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_URI] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_MimeType] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [s_ObjectSource] [nvarchar] (25)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [sc_Status] [int] NULL ,
[s_cache_info] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNbr1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNbr2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SessionID] [int] NULL , [ConnectionID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DbWC3ISAPACLog] (
[Machine] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogFileName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [AccessDate] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [AccessTime] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL , [c_IP] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_IP] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Protocol] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Param1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Param2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tcp_flags] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_filter_rule] [int] NULL , [s_Interface] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [rs_ip_header] [varchar] (2000)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [rs_payload] [varchar]
(2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DbWC3ISAWEBLog ] (
[ReportingMachine] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [LogFileName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [c_IP] [nvarchar] (18) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [cs_UserName] [nvarchar] (25)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [c_Agent] [nvarchar] (255)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [sc_Authenticated] [nchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AccessDate] [nvarchar]
(8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AccessTime] [nvarchar]
(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [s_SvcName]
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_ComputerName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_Referred] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_Host] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_IP] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [r_Port] [int] NULL , [Time_Taken]
[int] NULL , [cs_Bytes] [int] NULL , [sc_Bytes] [int] NULL ,
[cs_Protocol] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [cs_Transport] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [s_Opertion] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_URI] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [cs_MimeType] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , [s_ObjectSource] [nvarchar] (25)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [sc_Status] [int] NULL ,
[s_cache_info] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNbr1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNbr2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DbWC3ISAFIRLog] WITH NOCHECK ADD
CHECK (isdate([AccessDate]) = 1 and [AccessDate] like
replicate('[0-9]',8)), CHECK (isdate([AccessTime]) = 1 and
len([AccessTime]) = 8 and [AccessTime] like
'[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
GO
CREATE INDEX [IX_DbWC3ISAFIRLog_AccessDate] ON
[dbo].[DbWC3ISAFIRLog]([AccessDate]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAFIRLog_AccessTime] ON
[dbo].[DbWC3ISAFIRLog]([AccessTime]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAFIRLog_csUserAgent] ON
[dbo].[DbWC3ISAFIRLog]([c_Agent]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAFIRLog_LogFileName] ON
[dbo].[DbWC3ISAFIRLog]([LogFileName]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAFIRLog_ReportingMachine] ON
[dbo].[DbWC3ISAFIRLog]([ReportingMachine]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DbWC3ISAPACLog] WITH NOCHECK ADD
CHECK (isdate([AccessDate]) = 1 and [AccessDate] like
replicate('[0-9]',8)), CHECK (isdate([AccessTime]) = 1 and
len([AccessTime]) = 8 and [AccessTime] like
'[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
GO
CREATE INDEX [IX_DbWC3ISAPACLog_AccessDate] ON
[dbo].[DbWC3ISAPACLog]([AccessDate]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAPACLog_AccessTime] ON
[dbo].[DbWC3ISAPACLog]([AccessTime]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAPACLog_Machine] ON
[dbo].[DbWC3ISAPACLog]([Machine]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAPACLog_LogFileName] ON
[dbo].[DbWC3ISAPACLog]([LogFileName]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAPACLog_Protocol] ON
[dbo].[DbWC3ISAPACLog]([Protocol]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DbWC3ISAWEBLog ] WITH NOCHECK ADD
CHECK (isdate([AccessDate]) = 1 and [AccessDate] like
replicate('[0-9]',8)), CHECK (isdate([AccessTime]) = 1 and
len([AccessTime]) = 8 and [AccessTime] like
'[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
GO
CREATE INDEX [IX_DbWC3ISAWEBLog_AccessDate] ON [dbo].[DbWC3ISAWEBLog
]([AccessDate]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAWEBLog_AccessTime] ON [dbo].[DbWC3ISAWEBLog
]([AccessTime]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAWEBLog_csUserAgent] ON [dbo].[DbWC3ISAWEBLog
]([c_Agent]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAWEBLog_LogFileName] ON [dbo].[DbWC3ISAWEBLog
]([LogFileName]) ON [PRIMARY]
GO
CREATE INDEX [IX_DbWC3ISAWEBLog_ReportingMachine] ON
[dbo].[DbWC3ISAWEBLog ]([ReportingMachine]) ON [PRIMARY] GO
------------------------------------------------------
List Archives: http://www.webelists.com/cgi/lyris.pl?enter=isalist
ISA Server Newsletter: http://www.isaserver.org/pages/newsletter.asp
ISA Server FAQ: http://www.isaserver.org/pages/larticle.asp?type=FAQ
------------------------------------------------------
Other Internet Software Marketing Sites:
Leading Network Software Directory: http://www.serverfiles.com No.1
Exchange Server Resource Site: http://www.msexchange.org Windows
Security Resource Site: http://www.windowsecurity.com/ Network Security
Library: http://www.secinf.net/ Windows 2000/NT Fax Solutions:
http://www.ntfaxfaq.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
jim@xxxxxxxxxxxx To unsubscribe send a blank email to
$subst('Email.Unsub')
------------------------------------------------------
List Archives: http://www.webelists.com/cgi/lyris.pl?enter=isalist
ISA Server Newsletter: http://www.isaserver.org/pages/newsletter.asp
ISA Server FAQ: http://www.isaserver.org/pages/larticle.asp?type=FAQ
------------------------------------------------------
Other Internet Software Marketing Sites:
Leading Network Software Directory: http://www.serverfiles.com No.1
Exchange Server Resource Site: http://www.msexchange.org Windows
Security Resource Site: http://www.windowsecurity.com/ Network Security
Library: http://www.secinf.net/ Windows 2000/NT Fax Solutions:
http://www.ntfaxfaq.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
robertson.william@xxxxxxxxxxxxxx To unsubscribe send a blank email to
$subst('Email.Unsub')
---------------------------------------------------------------------
Everything in this e-mail and attachments relating to the official
business of Columbus Stainless is proprietary to the company. It is
confidential, legally privileged and protected by law. Columbus
Stainless does not own and endorse any other content. Views and
opinions are those of the sender unless clearly stated as being that
of Columbus Stainless. The person addressed in the e-mail is the sole
authorised recipient. Please notify the sender immediately if it has
unintentionally reached you and do not read, disclose or use the
content in any way. Whilst all reasonable steps are taken to ensure
the accuracy and integrity of information and data transmitted
electronically and to preserve the confidentiality thereof, no
liability or responsibility whatsoever is accepted if information or
data is,for whatever reason, corrupted or does not reach its intended
destination.
---------------------------------------------------------------------
------------------------------------------------------
List Archives: http://www.webelists.com/cgi/lyris.pl?enter=isalist
ISA Server Newsletter: http://www.isaserver.org/pages/newsletter.asp
ISA Server FAQ: http://www.isaserver.org/pages/larticle.asp?type=FAQ
------------------------------------------------------
Other Internet Software Marketing Sites:
Leading Network Software Directory: http://www.serverfiles.com No.1
Exchange Server Resource Site: http://www.msexchange.org Windows
Security Resource Site: http://www.windowsecurity.com/ Network Security
Library: http://www.secinf.net/ Windows 2000/NT Fax Solutions:
http://www.ntfaxfaq.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
ddellanno@xxxxxxxxxx To unsubscribe send a blank email to
$subst('Email.Unsub')
Confidentiality Notice:
This e-mail message, including any attachments, is for the sole use of
the intended recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please contact the
sender by reply e-mail and destroy all copies of the original message.
Confidentiality Notice:
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain confidential and privileged information.
Any unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.
Other related posts:
