Welcome to ISAserver.org

Any free reporting plugins?

Users viewing this topic: none

Logged in as: Guest

Printable Version

All Forums >> [ISA 2006 General] >> General >> Any free reporting plugins?

Page: [1]

Message

<< Older Topic Newer Topic >>

Any free reporting plugins? - 10.Jun.2008 2:57:46 AM

gping

Posts: 11
Joined: 22.Jan.2007
Status: offline

Hi, could anyone introduce me some free (not trial version) plugins / utilities for generating user/clientIP/web/protocol reports?
I have written some sql scripts to grep data from the msde database, but not easy to operate.

thanks a lot for help.

Post #: 1

Featured Links*

RE: Any free reporting plugins? - 10.Jun.2008 11:24:54 AM

tshinder

Posts: 50013
Joined: 10.Jan.2001
From: Texas
Status: offline

None that I'm aware of. If you find any, let us know.

Thanks!
Tom

_____________________________

Thomas W Shinder, M.D.

(in reply to gping)

Post #: 2

RE: Any free reporting plugins? - 10.Jun.2008 7:22:40 PM

elmajdal

Posts: 6022
Joined: 16.Sep.2004
From: Lebanese in Kuwait
Status: offline

Hi,

I will contact Chris, and refer to him this thread, maybe he can add a new tool to his collection on http://sync-io.net/ISATools.aspx

_____________________________

Tarek Majdalani

Windows Expert - IT Pro MVP
Facebook : https://www.facebook.com/ElMajdal.Net

(in reply to tshinder)

Post #: 3

RE: Any free reporting plugins? - 10.Jun.2008 10:33:00 PM

fixitchris

Posts: 148
Joined: 23.May2007
Status: offline

gping, can I glance at your scripts to get an idea of what kind of reports you are looking for? Send them over to chris@sync-io.net.

< Message edited by fixitchris -- 10.Jun.2008 10:36:43 PM >

(in reply to gping)

Post #: 4

RE: Any free reporting plugins? - 11.Jun.2008 9:51:48 AM

tshinder

Posts: 50013
Joined: 10.Jan.2001
From: Texas
Status: offline

Hi Chris,

Thanks!

If there are any projects that you are working on and want ISAserver.org members to help you beta test, just let me know and I can put it up on the blog and on the news section on the front page of the site.

Thanks!
Tom

_____________________________

Thomas W Shinder, M.D.

(in reply to fixitchris)

Post #: 5

RE: Any free reporting plugins? - 16.Jun.2008 9:18:35 PM

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.

(in reply to fixitchris)

Post #: 6

RE: Any free reporting plugins? - 17.Jun.2008 8:59:14 AM

fixitchris

Posts: 148
Joined: 23.May2007
Status: offline

What is the name of the trial software? I could get ideas from there as to the range of needed reports.

SQL is great when it works.

Logparser2 from MS will parse w3c for you too.

(in reply to gping)

Post #: 7