کد:
http://www.isaserver.org/img/upl/oracle_logging.htm
By Euticio Montelongo
Part I: Install Oracle Client Software on ISA server.
You need to install Oracle Client Software on your local ISAserver. The SQLnet layer is needed for ODBC connectivity to work. You can install the minimum requirements since all you need is the SQLnet layer and Oracle ODBC driver. If you use custom install verify that Oracle's ODBC driver is included. However, Microsoft's Oracle ODBCdriver should work as well.
Exit Oracle installation.
Part II: Create Oracle user
Start
SQL*Plus from your ISA Server. You need to logon with an account that has create users, tables, and indexes priviledges. I used my system account. Of course, you can perform this operation on any computer with access to your Oracle database.
Modify the create user statement as needed. If you decide to change the user name be sure to modify all instances of the user name in all create statements. I would recommend changing the password and tablespace. I created a 2GB tablespace dedicated for ISA logging and assigned it to the ISA users default tablespace. The amount of logging information for me has been around 75-100MB a day.
Cut and paste the script below and save it to a file on your local hard drive. I choose
c:\temp\isalog.sql
کد:
CREATE USER "ISA" IDENTIFIED BY "ISA" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "ISA";
CREATE TABLE ISA.FirewallLog (
ClientIP varchar2(32),
ClientUserName varchar2(32),
ClientAgent varchar2(128),
ClientAuthenticate varchar2(5),
logDate date,
logTime date,
service varchar2(12),
servername varchar2(32),
referredserver varchar2(32),
DestHost varchar2(255),
DestHostIP varchar2(32),
DestHostPort number,
processingtime number,
bytessent number,
bytesrecvd number,
protocol varchar2(8),
transport varchar2(8),
operation varchar2(8),
uri varchar2(255),
mimetype varchar2(32),
objectsource varchar2(12),
resultcode number,
CacheInfo number,
rule#1 varchar2(128),
rule#2 varchar2(128),
sessionid number,
connectionid number
);
CREATE INDEX ISA.IX_FirewallLog_Date ON ISA.FirewallLog(logDate);
CREATE INDEX ISA.IX_FirewallLog_DateTime ON ISA.FirewallLog(logDate,
logTime);
CREATE TABLE ISA.PacketFilterLog (
PFlogDate date,
PFlogTime date,
SourceAddress varchar2(25),
DestinationAddress varchar2(25),
Protocol varchar2(8),
Param#1 varchar2(8),
Param#2 varchar2(8),
TcpFlags varchar2(255),
FilterRule varchar2(16),
Interface varchar2(25),
IPHeader varchar2(255),
Payload varchar2(255)
);
CREATE INDEX ISA.IX_PacketFilterLog_Date ON
ISA.PacketFilterLog(PFlogDate);
CREATE INDEX ISA.IX_PacketFilterLog_DateTime ON
SA.PacketFilterLog(PFlogDate, PFlogTime);
CREATE TABLE ISA.WebProxyLog (
ClientIP varchar2(32),
ClientUserName varchar2(32),
ClientAgent varchar2(128),
ClientAuthenticate varchar2(5),
logDate date,
logTime date,
service varchar2(16),
servername varchar2(32),
referredserver varchar2(32),
DestHost varchar2(255),
DestHostIP varchar2(32),
DestHostPort number,
processingtime number,
bytesrecvd number,
bytessent number,
protocol varchar2(12),
transport varchar2(8),
operation varchar2(8),
uri varchar2(255),
mimetype varchar2(32),
objectsource varchar2(12),
resultcode number,
CacheInfo number,
rule#1 varchar2(128),
rule#2 varchar2(128)
);
CREATE INDEX ISA.IX_WebProxyLog_Date ON ISA.WebProxyLog(logDate);
CREATE INDEX ISA.IX_WebProxyLog_DateTime ON ISA.WebProxyLog(logDate,
logTime);
COMMIT;
Within
SQL*Plus execute the SQL file as follow:
@c:\temp\isalog.sql
If you saved this file somewhere else or with a different name you'll need to specify a different path or filename.
@<saved directory>\<filename>
Exit
SQL*Plus.
Part III: Create Oracle System DSN
Start
ODBC DataSource Administrator located in the Administrator folder. Create a System DSN for your Oracle database. I used Oracle's ODBC Driver. Name your Data Source Name
ISAORA and be sure to include the UserID (Oracle) or Username (Microsoft Oracle).
Exit ODBC DataSource Administrator.
Start regedit and locate
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\ISAORA and create a string key called
Password and fill in your user password. I have included the following registry sample below. You only need to modify the password.
کد:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ISAORA]
"Password"="isa"
Exit registry editor.
Part IV: Install and configure translation gateway
Fancy phrase for using Microsoft Access. You'll need to install Microsoft Access 97, 2000 or 2002. Once installed, create a blank database called
isalog.mdb within your ISA server installation directory.
C:\Program Files\Microsoft ISA Server\isalog.mdb
Open
isalog.mdb if you haven't already.
We'll be using Microsoft Access as our translation gateway between our ISA server and Oracle by linking the user isa tables.
Click
File->
Get External Data->
Link Tables.
Under
Link dialog box select
ODBC databases under the
Files of Type. This will start the
Select Data Source dialog.
Click Machine Data Source and double click
ISAORA. If you are prompted for a password then you have incorrectly modified the Password registry entry and need to repeat the last step before you can proceed.
Select
ISA.FIREWALLOG,
ISA.PACKETFILTERLOG and
ISA.WEBPROXYLOG. Click
OK. You will be prompted to select a unique record identifier, just click
OK for each table. You will now have three linked tables named
ISA_FIREWALLLOG,
ISA_PACKETFILTERLOG, and
ISA_WEBPROXYLOG. If you accidently included other tables you can safely delete them. Right click and select
delete.
Now you will need to rename each table. Remove the prefix "
ISA_" from each table. Right click and select
rename. You will now have three tables named
FIREWALLLOG,
PACKETFILTERLOG,
WEBPROXYLOG. When you are finished you should only have three linked tables in this database.
Exit Microsoft Access.
Part V: Create Access System DSN
Start
ODBC DataSource Administrator located in the Administrator folder. Create a System DSN for your Access database. Select
Microsoft Access Driver. Name your Data Source Name
ISALOG. Click
Select and locate the
isalog.mdb database under
C:\Program Files\Microsoft ISA Server. Click
OK. Click
OK. When you are finished you should have two System Data Sources, called
ISAORA and
ISALOG.
ISAORA using the Oracle ODBC driver or Microsoft Oracle driver and
ISALOG using Microsoft Access driver.
Exit ODBC DataSource Administrator.
Part VI: Test translation gateway
Oracle provides an ODBC testing tool located under
Start->
Programs->
Oracle->
Network Administration. Click
Oracle ODBC Test. Click
Connect. Click
Machine Data Source. Click
ISALOG. Be sure to select
ISALOG, we need to test from Access side of things. Click
OK. Within the dialog box below the connect button type "
select count(*) from webproxylog" without the quotes and click
execute. You should get result of zero. If you are prompted for a password then you will need to verify that you have correctly included the Password registry entry. We have just verified that Access is able to communicate with Oracle.
Part VII: Configure ISA Logging. - Finally
Start the
ISA Management console.
Configure Packet Filter Logging:
Click
<Your server>->Monitoring Configuration->Logs. Double click
Packet filters. Under the
Log tab select
Database. Fill in
ISALOG for
ODBC data source (DSN). Fill in
PacketFilterLog for
Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify
Enable logging for this service is checked. Click
OK.
Configure ISA Server Firewall service Logging:
Click
<Your server>->Monitoring Configuration->Logs. Double click
ISA Server Firewall service. Under the
Log tab select
Database. Fill in
ISALOG for
ODBC data source (DSN). Fill in
FirewallLog for
Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify
Enable logging for this service is checked. Click
OK.
Configure Packet Filter Logging:
Click
<Your server>->Monitoring Configuration->Logs. Double click
ISA Server Web Proxy Service. Under the
Log tab select
Database. Fill in
ISALOG for
ODBC data source (DSN). Fill in
WebProxyLog for
Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify
Enable logging for this service is checked. Click
OK.
Feel free to select change the field logging for each service.
You have now successfully configured ISA to log to an Oracle Database.
Enjoy!
Be sure to watch you tablespace. ISA does a lot of logging.