gping
Posts: 11
Joined: 22.Jan.2007
Status: offline
|
Hi, Chris, Thanks for your reply to my post at isaserver.org. I am using MSDE to log isa records. I have wrote some SP to convert such as HEX ip address into normal xxx.xxx.xxx.xxx, And a script “Report” to run as Report “mm/dd”, “IP_address”, “start_time”, “end_time” to check the usage and contents, then summarize them etc. CREATE PROCEDURE webrpt @date varchar(4), @ipaddr varchar(15), @starttime varchar(4), @endtime varchar(4) AS declare @sql varchar(1000), @mdate varchar(20), @mstarttime varchar(20), @mendtime varchar(20), @mipaddr varchar(40), @msourceip varchar(40), @mdestip varchar(40) set @mdate = '2007' + right('0000'+@date,4) set @mstarttime = right('0000'+@starttime,4) set @mendtime = right('0000'+@endtime,4) set @mipaddr = '"'+@ipaddr+'%"' set @mstarttime = '"'+@mdate+' '+left(@mstarttime,2)+":"+right(@mstarttime,2)+'"' set @mendtime = '"'+@mdate+' '+left(@mendtime,2)+":"+right(@mendtime,2)+'"' set @msourceip = 'master.dbo.gp_ip(ClientIP)' set @mdestip = 'master.dbo.gp_ip(DestHostIP)' set @sql = 'select ' + @mdate + ' as Date, master.dbo.gp_hhmmss(logTime) as Time, ' set @sql = @sql + ' ClientUserName AS Client, ' set @sql = @sql + @msourceip + ' AS Source_IP, ' set @sql = @sql + ' DestHost, ' set @sql = @sql + @mdestip + ' AS Dest_IP, transport '+"+':'+"+' convert(varchar,DestHostPort) AS DPort, ' set @sql = @sql + ' uri, ' set @sql = @sql + ' bytessent, bytesrecvd, ' set @sql = @sql + ' ClientAgent AS APPS ' set @sql = @sql + ' from ISALOG_' + @mdate + '_WEB_000.dbo.WebProxyLog ' set @sql = @sql + ' where logtime > ' + @mstarttime + ' and logtime < ' + @mendtime set @sql = @sql + ' and (bytessent >0 or bytesrecvd >0) ' set @sql = @sql + ' and (' + @msourceip + ' like ' + @mipaddr + ' or ' + @mdestip + ' like ' + @mipaddr + ')' exec (@sql) --print @sql GO I download some trial ISA log analyzer, most of them, if not all, use W3C extended log files. I personally prefer to dump all log into a single sql database. Question: which way would you recommend? txt files are easily controlled over the disk size issue, while SQL is really easy to query.
|