ISA logs to SQL reports and database maintenance

ISA logs to SQL reports and database maintenance

Post by Mik Ros » Fri, 14 Sep 2001 05:18:29



Ok this is the scenario. I have an ISA Server that sends all its logs to an
SQL 200 database. 3 databases as a matter of fact for each service which are
fwsrv, pf and W3proxy. I have found that these databases can grow very
quickly! So what I was trying to do is generate stats for database:w3proxy
table: WebProxyLog colums: ClientIP ClientUserName ClientAgent logDate
logTime DestHost DestIP processingtime bytesrecvd bytessent operation uri
obectsource resultcode CacheInfo rule#1 rule#2. For database:fwsrv tables:
FirewallLog colums: bytessent bytesrecvd protocol ClientIP logDate. For
Proxy Filter logs database:pf tables: PacketFilterLog colums: Pflogdate
PFlogTime SourceAddress DestinationAddress Protocol Param#1 Param#2 TcpFlags
FilterRule IPHeader Payload

Once these stats were generated and produced in XML to view on a www page I
wanted to have a stored / scheduled procedure execute some type pruning
activity to the database for the period that it generated the stats for so
that it removes the rows for each database for the date period.

Can anyone help me out...?


 
 
 

ISA logs to SQL reports and database maintenance

Post by oj » Fri, 14 Sep 2001 11:26:59


ah, just posted an answer to your earlier post...

------------------------------------------------
Mik,

Just like Ivan said, please post DDL (create table statement) and sample data (insert table statemen) and a brief layout as to what you're trying to accomplish. It's very hard to come up with a sound solution for you without these.

Anyway, here is my guess...

SELECT logDate AS date, DestHost AS dest_host, ClientIP AS cl_ip, COUNT(*) AS Count_Per_Host
FROM         WebProxyLog
WHERE     (logDate >= GETDATE() - 7)
GROUP BY logDate, DestHost, ClientIP

-oj


  Ok this is the scenario. I have an ISA Server that sends all its logs to an
  SQL 200 database. 3 databases as a matter of fact for each service which are
  fwsrv, pf and W3proxy. I have found that these databases can grow very
  quickly! So what I was trying to do is generate stats for database:w3proxy
  table: WebProxyLog colums: ClientIP ClientUserName ClientAgent logDate
  logTime DestHost DestIP processingtime bytesrecvd bytessent operation uri
  obectsource resultcode CacheInfo rule#1 rule#2. For database:fwsrv tables:
  FirewallLog colums: bytessent bytesrecvd protocol ClientIP logDate. For
  Proxy Filter logs database:pf tables: PacketFilterLog colums: Pflogdate
  PFlogTime SourceAddress DestinationAddress Protocol Param#1 Param#2 TcpFlags
  FilterRule IPHeader Payload

  Once these stats were generated and produced in XML to view on a www page I
  wanted to have a stored / scheduled procedure execute some type pruning
  activity to the database for the period that it generated the stats for so
  that it removes the rows for each database for the date period.

  Can anyone help me out...?



 
 
 

1. accessing ISA logs from SQL database

Hi All,

As i configured ISA server it allows me to export logs to
dtabase servers .. So i configured SQL server for that
purpose ..& it was working fine ..but few days back my
collegue change d the password of the local administrator
since then it is not working or  it doesn't store any
information but only empty files..I tried to reinstall SQL
serer but still it's not happening ..
looking forward for your positive response

rgds

topsy

2. app_ctl_heap_sz

3. SQL 7 Database Maintenance Plans Report emails

4. SQL queries

5. ISA logging to SQL Server

6. problem with view (after restore)

7. Database Maintenance plan not removing log files

8. WebDB always does a LIKE query???

9. Database maintenance plan backing up logs

10. Database maintenance- does not truncate logs?

11. Database Maintenance Plan Reporting Deletion

12. ISA slow with logical logs

13. Question about logging ISA server