RE: SQL Logging

• From: "cismic" <cismic@xxxxxxx>
• To: "'[ISAserver.org Discussion List]'" <isalist@xxxxxxxxxxxxx>
• Date: Fri, 30 May 2003 09:39:43 -0700

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

Other related posts:

• » SQL Logging
• » RE: SQL Logging
• » Re: SQL Logging
• » RE: SQL Logging
• » SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » RE: SQL Logging
• » SQL Logging
• » Re: SQL Logging
• » Re: SQL Logging

1. Home
2. isalist
3. Archive
4. 05-2003

---

• » Previous by Date
• » Next by Date
• » Related Posts
• » View list details
• » Manage your subscription

---