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
/
25066
< prev
next >
Wrap
Text File
|
2010-06-15
|
4KB
|
110 lines
CREATE PROCEDURE [dbo].[ISA_spSiteSummaryTrim]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID ('tblSiteSummary', 'u') IS NULL
RETURN
DECLARE @FromDate datetime
SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
CREATE TABLE #Table1
(
[Site] varchar(255),
[BytesIn] bigint,
[BytesOut] bigint,
[TotalBytes] bigint,
[Requests] bigint,
[URLCategoryId] int,
[MalwareIncidents] bigint,
[MalwareInspectionDuration] bigint,
[Action] int,
[Date] datetime
)
INSERT INTO #Table1
SELECT * FROM [tblSiteSummary] WHERE [MalwareIncidents] > 0
DECLARE @SQLString1 nvarchar(2000) =
'SELECT *
FROM (
SELECT
[Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date]
FROM (
SELECT
[Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date],
ROW_NUMBER() OVER (PARTITION BY [Action] ORDER BY '
DECLARE @SQLString2 nvarchar(2000) =
' DESC) AS rownum
FROM dbo.[tblSiteSummary]
WHERE '
DECLARE @SQLString3 nvarchar(2000) =
' > 0 ) t
WHERE rownum <= 5000
) ttt
WHERE [Site] IS NOT NULL
AND [MalwareIncidents] = 0
AND NOT EXISTS (SELECT * FROM #Table1
WHERE ttt.[Site] = [Site] AND
ttt.[BytesIn] = [BytesIn] AND
ttt.[BytesOut] = [BytesOut] AND
ttt.[TotalBytes] = [TotalBytes] AND
ttt.[Requests] = [Requests] AND
ttt.[URLCategoryId] = [URLCategoryId] AND
ttt.[MalwareIncidents] = [MalwareIncidents] AND
ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
ttt.[Action] = [Action]
)'
DECLARE @SQLString nvarchar(4000)
DECLARE @OrderString nvarchar(200)
SET @OrderString = '[Requests]'
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[TotalBytes]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[BytesIn]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[BytesOut]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
---- now add tail
INSERT INTO #TABLE1
SELECT
N'{[23124]}' AS [Site],
SUM([BytesIn]),
SUM([BytesOut]),
SUM([TotalBytes]),
SUM([Requests]),
' ' AS [URLCategoryId],
0 AS [MalwareIncidents],
SUM([MalwareInspectionDuration]),
[Action],
@FromDate
FROM dbo.[tblSiteSummary] ttt
WHERE
[MalwareIncidents] = 0 AND
NOT EXISTS (SELECT *
FROM #Table1
WHERE ttt.[Site] = [Site] AND
ttt.[BytesIn] = [BytesIn] AND
ttt.[BytesOut] = [BytesOut] AND
ttt.[TotalBytes] = [TotalBytes] AND
ttt.[Requests] = [Requests] AND
ttt.[URLCategoryId] = [URLCategoryId] AND
ttt.[MalwareIncidents] = [MalwareIncidents] AND
ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
ttt.[Action] = [Action])
GROUP BY
[Action],
CASE
WHEN [MalwareInspectionDuration] = 0 THEN 0
ELSE 1
END
TRUNCATE TABLE [tblSiteSummary]
INSERT INTO [tblSiteSummary]
SELECT *
FROM #TABLE1
END