Re: ISA Server Log Files
- From: "josephk" <josephk@xxxxxxxxx>
- To: "[ISAserver.org Discussion List]" <isalist@xxxxxxxxxxxxx>
- Date: Tue, 21 Sep 2004 11:38:26 -0700
The SQL creates a summary table that looks like this and emulates a
pivot table:
SummaryYear SummaryMonth SummaryDay AccessDate Hour00 Hour01
ETC.
----------- ------------ ----------- ---------- ----------- -----------
ETC.
2002 1 8 20020108 0 0
2002 1 9 20020109 0 2
You should see my CUBES!
Joseph
-----Original Message-----
From: josephk
Sent: Tuesday, September 21, 2004 11:25 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: ISA Server Log Files
http://www.ISAserver.org
Yeah then you can start doing wonderful things like this:
This is just a simple first time update into and for the Creation of a
table.
/****************************************************************
Module/sp Name: admUpdateFIR_DD_ByDateByAccessDate
Version: 1.00.0000
Description: This routine Processes the daily web log and creates
summary information to be used to determine the total
Number of hits per web site on a monthly basis by day
this data is refreshed with end of month processing.
Last update: 4/27/2002 , Joseph Kravis josephk@xxxxxxxxx
Notes:
** REVISIONS**
Date By Description
-------- ---------- ------------------------------------------
See individual Sections for revision information
EXEC admUpdateFIR_DD_ByDateByAccessDate
drop admUpdateFIR_DD_ByDateByAccessDate
****************************************************************/
CREATE PROCEDURE dbo.admUpdateFIR_DD_ByDateByAccessDate
AS
BEGIN
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
--INSERT INTO ISA_PRXLog_DD_ByDateByAccessDate
SELECT
YEAR(AccessDate) as 'SummaryYear',
MONTH(AccessDate) as 'SummaryMonth',
Day(AccessDate) as 'SummaryDay' ,
AccessDate as 'AccessDate',
SUM(CASE left(AccessTime,2)
WHEN '00' THEN 0
ELSE 0
END) as 'Hour00',
SUM(CASE left(AccessTime,2)
WHEN '01' THEN 1
ELSE 0
END) as 'Hour01',
SUM(CASE left(AccessTime,2)
WHEN '02' THEN 1
ELSE 0
END) as 'Hour02',
SUM(CASE left(AccessTime,2)
WHEN '03' THEN 1
ELSE 0
END) as 'Hour03',
SUM(CASE left(AccessTime,2)
WHEN '04' THEN 1
ELSE 0
END) as 'Hour04',
SUM(CASE left(AccessTime,2)
WHEN '05' THEN 1
ELSE 0
END) as 'Hour05',
SUM(CASE left(AccessTime,2)
WHEN '06' THEN 1
ELSE 0
END) as 'Hour06',
SUM(CASE left(AccessTime,2)
WHEN '07' THEN 1
ELSE 0
END) as 'Hour07',
SUM(CASE left(AccessTime,2)
WHEN '08' THEN 1
ELSE 0
END) as 'Hour08',
SUM(CASE left(AccessTime,2)
WHEN '09' THEN 1
ELSE 0
END) as 'Hour09',
SUM(CASE left(AccessTime,2)
WHEN '10' THEN 1
ELSE 0
END) as 'Hour10',
SUM(CASE left(AccessTime,2)
WHEN '11' THEN 1
ELSE 0
END) as 'Hour11',
SUM(CASE left(AccessTime,2)
WHEN '12' THEN 1
ELSE 0
END) as 'Hour12',
SUM(CASE left(AccessTime,2)
WHEN '13' THEN 1
ELSE 0
END) as 'Hour13',
SUM(CASE left(AccessTime,2)
WHEN '14' THEN 1
ELSE 0
END) as 'Hour14',
SUM(CASE left(AccessTime,2)
WHEN '15' THEN 1
ELSE 0
END) as 'Hour15',
SUM(CASE left(AccessTime,2)
WHEN '16' THEN 1
ELSE 0
END)as 'Hour16',
SUM(CASE left(AccessTime,2)
WHEN '17' THEN 1
ELSE 0
END)as 'Hour17',
SUM(CASE left(AccessTime,2)
WHEN '18' THEN 1
ELSE 0
END)as 'Hour18',
SUM(CASE left(AccessTime,2)
WHEN '19' THEN 1
ELSE 0
END) as 'Hour19',
SUM(CASE left(AccessTime,2)
WHEN '20' THEN 1
ELSE 0
END) as 'Hour20',
SUM(CASE left(AccessTime,2)
WHEN '21' THEN 1
ELSE 0
END) as 'Hour21',
SUM(CASE left(AccessTime,2)
WHEN '22' THEN 1
ELSE 0
END) as 'Hour22',
SUM(CASE left(AccessTime,2)
WHEN '23' THEN 1
ELSE 0
END )as 'Hour23'
INTO ISA_FIRLog_DD_ByDateByAccessDate
FROM ISA_FIRLog
GROUP BY YEAR(AccessDate), MONTH(AccessDate), DAY(AccessDate),
AccessDate ORDER BY YEAR(AccessDate), MONTH(AccessDate),
DAY(AccessDate), AccessDate
--Update The month field for better sorting
UPDATE ISA_FIRLog_DD_ByDateByAccessDate
SET SummaryMonth =
CASE
WHEN SummaryMonth < 10
THEN '0' + SummaryMonth
ELSE SummaryMonth
END
-- Returns 0 if some statements had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
END
GO
-----Original Message-----
From: Jim Harrison [mailto:jim@xxxxxxxxxxxx]
Sent: Tuesday, September 21, 2004 11:19 AM
To: [ISAserver.org Discussion List]
Subject: [isalist] Re: ISA Server Log Files
http://www.ISAserver.org
You have to use osql (installed by default) as:
C:>osql -S isaservername\msfw$ -E
..this connects you to MSDE as the interactive account.
Note that you MUST be logged on as an ISA local administrator and this
ONLY works at the ISA itself; neither ISA nor MSDE allow
remote connections.
..from here, you can use your incredible knowledge of T-SQL commands to
query the log databases.
Jim Harrison
MCP(NT4, W2K), A+, Network+, PCG
http://isaserver.org/Jim_Harrison/
http://isatools.org
Read the help / books / articles!
----- Original Message -----
From: "Mustafa Cicek" <mbcicek@xxxxxxxxx>
To: "[ISAserver.org Discussion List]" <isalist@xxxxxxxxxxxxx>
Sent: Tuesday, September 21, 2004 12:05
Subject: [isalist] Re: ISA Server Log Files
http://www.ISAserver.org
Hi Jim!
How can I access MSDE? I don't know sa password for the databases on it.
There was no question about password by installation.
Best Regards
Mustafa
------------------------------------------------------
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:
World of Windows Networking: http://www.windowsnetworking.com 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:
jim@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
------------------------------------------------------
Other Internet Software Marketing Sites:
World of Windows Networking: http://www.windowsnetworking.com 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:
josephk@xxxxxxxxxxxxxxxxx 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
------------------------------------------------------
Other Internet Software Marketing Sites:
World of Windows Networking: http://www.windowsnetworking.com 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:
josephk@xxxxxxxxxxxxxxxxx To unsubscribe visit
http://www.webelists.com/cgi/lyris.pl?enter=isalist
Report abuse to listadmin@xxxxxxxxxxxxx
Other related posts:
