home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
moodle.waes.ac.uk
/
moodle.waes.ac.uk.zip
/
moodle.waes.ac.uk
/
TMG
/
SP1-TMG-KB981324-AMD64-ENU.msp
/
PCW_CAB_SHFx2
/
F2143_msfpcui.dll
/
BINARY
/
25494
< prev
next >
Wrap
Text File
|
2010-06-15
|
4KB
|
105 lines
CREATE PROCEDURE [dbo].[ISA_spAppTopUsersRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int,
@SortOrder varchar(100)
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblUserSummary', @FromDate, @ToDate, @ReportType)
DECLARE @OrderColumn varchar(100)
SET @OrderColumn =
CASE
WHEN @SortOrder = 'Requests' THEN 'Requests'
WHEN @SortOrder = 'BytesIn' THEN 'BytesIn'
WHEN @SortOrder = 'BytesOut' THEN 'BytesOut'
ELSE 'TotalBytes'
END;
CREATE TABLE #Table1
(
UserName nvarchar(514),
Requests bigint,
BytesIn bigint,
BytesOut bigint,
TotalBytes bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
[UserName] ,
SUM([Requests]) - SUM([WebRequests]) AS Requests,
SUM([BytesIn]) - SUM([WebBytesIn]) AS BytesIn,
SUM([BytesOut]) - SUM([WebBytesOut]) AS BytesOut,
SUM([TotalBytes]) - SUM([WebTotalBytes]) AS TotalBytes
FROM ' + @SummTableName + '
WHERE
([Requests] - [WebRequests]) >0 AND
([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
GROUP BY
[UserName]
ORDER BY ' + QUOTENAME(@OrderColumn) + ' DESC;
'
INSERT #Table1
EXEC(@strQuery)
END
CREATE TABLE #Table2
(
UserName nvarchar(514),
Requests bigint,
BytesIn bigint,
BytesOut bigint,
TotalBytes bigint
)
INSERT INTO #Table2
SELECT TOP(@TopCount)
[UserName] AS UserName,
[Requests] AS Requests,
[BytesIn] AS BytesIn,
[BytesOut] AS BytesOut,
[TotalBytes] AS TotalBytes
FROM #Table1
WHERE [UserName] != N'{[23124]}'
INSERT INTO #Table2
SELECT
N'{[23124]}' As UserName,
SUM(Requests) AS Requests,
SUM([BytesIn]) AS BytesIn,
SUM([BytesOut]) AS BytesOut,
SUM([TotalBytes]) AS TotalBytes
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT *
FROM #Table2 t2
WHERE t1.UserName = t2.UserName)
DECLARE @TotalRequests bigint
SET @TotalRequests = (SELECT SUM(Requests) FROM #Table2 )
IF (@TotalRequests = 0)
SET @TotalRequests = 1
DECLARE @totalBytes bigint
SET @totalBytes = (SELECT SUM(TotalBytes) FROM #Table2 )
IF (@totalBytes = 0)
SET @totalBytes = 1
DECLARE @TotalBytesIn bigint
SET @TotalBytesIn = (SELECT SUM(BytesIn) FROM #Table2)
IF (@TotalBytesIn = 0)
SET @TotalBytesIn = 1
DECLARE @TotalBytesOut bigint
SET @TotalBytesOut = (SELECT SUM(BytesOut) FROM #Table2)
IF (@TotalBytesOut = 0)
SET @TotalBytesOut = 1
SELECT
[dbo].[fnUserOrIpAddressToText]([UserName]) AS [UserName],
[Requests] AS Requests,
(CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio,
[BytesIn] AS BytesIn,
(CAST([BytesIn] AS decimal) / @TotalBytesIn) AS BytesInRatio,
[BytesOut] AS BytesOut,
(CAST([BytesOut] AS decimal) / @TotalBytesOut) AS BytesOutRatio,
[TotalBytes] AS TotalBytes,
(CAST([TotalBytes] AS decimal) / @totalBytes) AS TotalByteRatio
FROM #Table2
END