RE: SQL Logging

  • From: "Quillman Shawn (RBNA/CIT1.1) *" <Shawn.Quillman@xxxxxxxxxxxx>
  • To: "'[ISAserver.org Discussion List]'" <isalist@xxxxxxxxxxxxx>
  • Date: Mon, 2 Jun 2003 07:15:02 -0500

Hmm.  Interesting about the separate databases.  Did you get any specific
errors if you had all the tables in one database?

Thanks for the feedback!

-Shawn

-----
Shawn R. Quillman
Robert Bosch Corporation RBNA/CIT1.1
38000 Hills Tech Drive
Farmington Hills, MI  48331
(248) 553-1164 (P)     (248) 848-2855 (F)
shawn.quillman@xxxxxxxxxxxx


-----Original Message-----
From: Vinay, G (ICRISAT-IN) [mailto:g.vinay@xxxxxxxxx]
Sent: Monday, June 02, 2003 4:09 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging


http://www.ISAserver.org


Hi shawn,
       We got it to work. we did the following:
1) separate database for each service
2) provide login permissions thru 'sa' sql login id, if the database is on a
remote sql server...

Vinay.
MCSE, CCNA

-----Original Message-----
From: Quillman Shawn (RBNA/CIT1.1) *
[mailto:Shawn.Quillman@xxxxxxxxxxxx]
Sent: Friday, May 30, 2003 7:20 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging


http://www.ISAserver.org



I guess I was wrong about that field.  I just ran the web proxy sql script
on my sql server and the table was created just find.  How did you define
your table, with the w3proxy.sql script on the ISA CD?  

If the web proxy service is not starting because it can't open its log
destination they you have a problem with your database connection.  It could
be a few things:

- Your ODBC data source configuration is not correct
- ISA does not have permission to update the table(s) in your database (or
maybe even connect to your database)
- If you already have the database created, your table definition is wrong
for the fields you are trying to log to.

Assuming your database is created, try opening Query Analyzer on your ISA
and run a query against your database using whatever account you configured
in your web proxy log settings.  Just a simple SELECT will work - SELECT *
FROM WebProxyLog.  That'll ensure that you can at least connect to the
database.  If you don't get an error then it works.  You may get an empty
result set depending on if you have data in the table or not.  After that
try an insert (this assumes you have all fields defined in your table and
that your table is named WebProxyLog):

INSERT INTO WebProxyLog
(ClientIP,ClientUserName,ClientAgent,ClientAuthenticate,logDate,logTime,serv
ice,servername,referredserver,DestHost,DestHostIP,DestHostPort,processingtim
e,bytesrecvd,bytessent,protocol,transport,operation,uri,mimetype,objectsourc
e,resultcode,CacheInfo,rule#1,rule#2)
VALUES
('1.1.1.1','joeuser','IE5','Y','2003-5-30','1899-12-30 9:00:00
AM','w3proxy','MYISASVR','-','-','-','-',100,1024,32,'http','TCP','GET','htt
p://www.google.com','text/html','Direct',304,0,'myrule1','myrule2')

This will tell you if ISA can write to the database.  If both of these work,
then it's probably your ODBC data source configuration.

-Shawn

-----
Shawn R. Quillman
Robert Bosch Corporation RBNA/CIT1.1
38000 Hills Tech Drive
Farmington Hills, MI  48331
(248) 553-1164 (P)     (248) 848-2855 (F)
shawn.quillman@xxxxxxxxxxxx


-----Original Message-----
From: Vinay, G (ICRISAT-IN) [mailto:g.vinay@xxxxxxxxx]
Sent: Friday, May 30, 2003 1:27 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging


http://www.ISAserver.org


HI shawn,
         ISA is logging to text files, and also to SQL, which i have
installed on ISA box. Can i get some precise field
which is creating the problem. I am trying to implement the same mentioned
by u.

Regards,
Vinay.

-----Original Message-----
From: Quillman Shawn (RBNA/CIT1.1) *
[mailto:Shawn.Quillman@xxxxxxxxxxxx]
Sent: Thursday, May 29, 2003 5:49 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] RE: SQL Logging


http://www.ISAserver.org



Did you mean that ISA will only log to local text files or do you have SQL
on your ISA box?  I seem to remember an error in the SQL script for creating
the web proxy log table on the ISA distribution.  There was a field that was
defined improperly.  I can't remember, I will go back and look.

Something you can try is taking one of your text logs and importing it into
SQL to make sure the table definitions are correct.  If that works then you
need to make sure ODBC is configured correctly for your envrionment.

-Shawn

-----
Shawn R. Quillman
Robert Bosch Corporation RBNA/CIT1.1
38000 Hills Tech Drive
Farmington Hills, MI  48331
(248) 553-1164 (P)     (248) 848-2855 (F)
shawn.quillman@xxxxxxxxxxxx


-----Original Message-----
From: Vinay, G (ICRISAT-IN) [mailto:g.vinay@xxxxxxxxx]
Sent: Thursday, May 29, 2003 4:13 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] SQL Logging


http://www.ISAserver.org



Hi All,
       I want to log the webproxy requests in SQL database instead of a text
file on ISA. I tried the option, but
ISA is logging in only on local SQL database, where as the webproxy service
does not start, if i try to log the requests
to remote sql database on some other machine. ANyone has some idea of this?
Please advice.


Regards,
Vinay.
MCSE,CCNA.   

------------------------------------------------------
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:
shawn.quillman@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:
g.vinay@xxxxxxxxx
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:
shawn.quillman@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:
g.vinay@xxxxxxxxx
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:
shawn.quillman@xxxxxxxxxxxx
To unsubscribe send a blank email to $subst('Email.Unsub')


Other related posts: