Re: usage reports by user.
- From: <tim.altena@xxxxxxxxxxxxxx>
- To: <isalist@xxxxxxxxxxxxx>
- Date: Mon, 3 Oct 2005 14:27:28 -0500
Just for everyone's information, not that you necessarily care, I got OSQL to
work with ISA's MSDE logs. I used the following command-line to connect to the
DB:
C:\>osql -E -S (local)\msfw -d ISALOG_20050928_WEB_000
1> SELECT [ClientUsername], [DestHost], Count([DestHost]) AS
[CountOfDestinationHostName]
2> FROM WebProxyLog
3> GROUP BY [ClientUsername], [DestHost]
4> ORDER BY [ClientUsername], [CountOfDestinationHostName]DESC;
5> GO
Or I could have done
C:\>osql -E -S (local)\msfw
1> USE ISALOG_20050928_WEB_000
2> SELECT [ClientUsername], [DestHost], Count([DestHost]) AS
[CountOfDestinationHostName]
3> FROM WebProxyLog
4> GROUP BY [ClientUsername], [DestHost]
5> ORDER BY [ClientUsername], [CountOfDestinationHostName]DESC;
6> GO
Using Jim's example I could not connect to the table that I needed too. I
still haven't got his query to run right yet but I'll keep on trying, thanks
again for everyone's help.
Tim
-----Original Message-----
From: tim.altena@xxxxxxxxxxxxxx [mailto:tim.altena@xxxxxxxxxxxxxx]
Sent: Monday, October 03, 2005 12:06 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
I've been trying and trying to get this to work via OSQL but no luck. I have
pared down the statement to just the basics and it still barfs.
1> select * FROM ISALOG_20051004_WEB_000
2> go
Msg 208, Level 16, State 1, Server OC05\MSFW, Line 1
Invalid object name 'ISALOG_20051004_WEB_000'.
1>
I have however been able to run a query in "Query Analyser" to get the data
out. But even in there the query statement that you gave me gives an error.
I am using the following query in Query Analyser and it seems to work but gives
me a list of all counts for each user not just the top 20.
SELECT [ClientUsername], [DestHost], Count([DestHost]) AS
[CountOfDestinationHostName]
FROM WebProxyLog
GROUP BY [ClientUsername], [DestHost]
ORDER BY [ClientUsername], [CountOfDestinationHostName]DESC;
This works but it would be nice to have it automated. Oh well can't have
everything when you know nothing. :)
Thanks for the help.
Tim
-----Original Message-----
From: Jim Harrison [mailto:Jim@xxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 12:35 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
Thanks for the correction, Shawn - I used it in logparser and 'assumed'...
The database format depends on your logs.
The actual format is: isalog_yyymmdd_###.
Note that you *must* use all three of the last digits.
If the DBName is isalog_20050929_001, then you type it in exactly that way.
-------------------------------------------------------
Jim Harrison
MCP(NT4, W2K), A+, Network+, PCG
http://isaserver.org/Jim_Harrison/
http://isatools.org
Read the help / books / articles!
-------------------------------------------------------
-----Original Message-----
From: tim.altena@xxxxxxxxxxxxxx [mailto:tim.altena@xxxxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 10:27
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
I got the same error.
-----Original Message-----
From: Quillman Shawn (RBNA/CSA1) * [mailto:Shawn.Quillman@xxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 11:30 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
Try this:
select [cs-username] as User, top 20 url as WebSite, mul( propcount( url ), 100
) as [%] from isalog_20050928_1 where [cs-username]='tima'
Gotta have the brackets around fieldnames where there are funky or otherwise
special characters involved (like % or -). SQL treats the - as subtraction
unless the brackets are there.
>-----
>Robert Bosch Corporation
>Technical Systems Analyst (RBNA/CSA1)
>Corporate Sales Reporting Systems
>38000 Hills Tech Drive - Farmington Hills, MI 48331 - USA
>phone: 1 (248) 876-1164 fax: 1 (248) 876-6969
>shawn.quillman@xxxxxxxxxxxx
>http://www.bosch.us
-----Original Message-----
From: tim.altena@xxxxxxxxxxxxxx [mailto:tim.altena@xxxxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 12:24 PM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
First of all thanks for your help so far. Second you have really jolted me out
of my comfort zone :)
I did some research on osql having never used it before but have found only
small tidbits of information. I have attempted to use the command that you
sent but I am getting a syntax error.
C:\Documents and Settings\tima>osql -E -S (local)\msfw
1> select cs-username as User, top 20 url as WebSite, mul( propcount( url ),
100 ) as % from isalog_20050928_1 where cs-username='tima'
2> go
Msg 156, Level 15, State 1, Server OC05\MSFW, Line 1 Incorrect syntax near the
keyword 'User'.
1>
First off I am hoping that the "yyyymmdd_###" I used is correct. I could not
find information on what I was to use for the ### part.
Thanks Again.
Tim
-----Original Message-----
From: Jim Harrison [mailto:Jim@xxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 10:39 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
Do this at the ISA itself:
Start | Run | cmd <enter>
(in the cmd window)
osql -E -S (local)\msfw
1> select cs-username as User, top 20 url as WebSite, mul( propcount( url ),
100 ) as % from isalog_yyymmdd_### where cs-username='JoeUser'
2> go
..of course, you'll have to replace "yyyymmdd_###' with the appropriate log
file to be searched.
-------------------------------------------------------
Jim Harrison
MCP(NT4, W2K), A+, Network+, PCG
http://isaserver.org/Jim_Harrison/
http://isatools.org
Read the help / books / articles!
-------------------------------------------------------
-----Original Message-----
From: tim.altena@xxxxxxxxxxxxxx [mailto:tim.altena@xxxxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 07:20
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
It is MSDE.
-----Original Message-----
From: Jim Harrison [mailto:Jim@xxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 9:14 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
No, he won't.
If the log format is either MSDE or SQL, a quick query will divulge the results.
-----Original Message-----
From: Tiago de Aviz [mailto:Tiago@xxxxxxxxxxxxxxx]
Sent: Thursday, September 29, 2005 6:48 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: usage reports by user.
http://www.ISAserver.org
http://sarg.sourceforge.net
But you'll have to practice sorcery.
If you don't want to head that way, There's bt-LogAnalyzer =)
www.burstek.com
Tiago de Aviz
SoftSell - Curitiba
(41) 3340-2363
www.softsell.com.br
Esta mensagem, incluindo seus anexos, tem caráter confidencial e seu conteúdo é
restrito ao destinatário da mensagem. Caso você tenha recebido esta mensagem
por engano, queira por favor retorná-la ao destinatário e apagá-la de seus
arquivos. Qualquer uso não autorizado, replicação ou disseminação desta
mensagem ou parte dela é expressamente proibido. A SoftSell não é responsável
pelo conteúdo ou a veracidade desta informação.
>>> tim.altena@xxxxxxxxxxxxxx 29/9/2005 10:42 >>>
http://www.ISAserver.org
I have been asked to generate a report of a specific user's web usage.
What they would like to see it the top 20 sites that the user has gone
to. I can see this being requested more often by other department heads
wanting to see this information for their employees.
Please correct me if I am wrong, but I do not see a way for ISA 2004 to
natively generate this type of report? Does anyone know of tool or
utility that will provide this report for me?
TIA
Tim
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tiago@xxxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
jim@xxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
All mail to and from this domain is GFI-scanned.
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tim.altena@xxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
jim@xxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
All mail to and from this domain is GFI-scanned.
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tim.altena@xxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
shawn.quillman@xxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tim.altena@xxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
jim@xxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
All mail to and from this domain is GFI-scanned.
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tim.altena@xxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
------------------------------------------------------
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
------------------------------------------------------
Visit TechGenix.com for more information about our other sites:
http://www.techgenix.com
------------------------------------------------------
You are currently subscribed to this ISAserver.org Discussion List as:
tim.altena@xxxxxxxxxxxxxx
To unsubscribe visit http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
Other related posts:
