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 / 25434 < prev    next >
Text File  |  2010-06-15  |  2KB  |  61 lines
  1. CREATE PROCEDURE [dbo].[ISA_spTopUserAuthFailuresRDL]
  2.     @FromDate datetime,
  3.     @ToDate datetime,
  4.     @ReportType varchar(10),
  5.     @TopCount int
  6. AS
  7. BEGIN
  8.     DECLARE @SummTableName varchar(100)
  9.     SET @SummTableName = dbo.fnGetSummaryTableName('tblUserSummary', @FromDate, @ToDate, @ReportType)
  10.     CREATE TABLE #Table1
  11.     (
  12.         UserName nvarchar(514),
  13.         AuthorizedFailures bigint,
  14.     )
  15.     IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
  16.     BEGIN
  17.         DECLARE @strQuery varchar(8000)
  18.         SET @strQuery =
  19.         'SELECT
  20.             [UserName],
  21.             SUM([AuthorizedFailures])
  22.         FROM ' + @SummTableName + '
  23.         WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
  24.             AND ([AuthorizedFailures] <> 0)
  25.         GROUP BY [UserName]
  26.         ORDER BY SUM([AuthorizedFailures]) DESC
  27.         '
  28.         INSERT #Table1
  29.         EXEC(@strQuery)
  30.     END
  31.     CREATE TABLE #Table2
  32.     (
  33.         UserName nvarchar(514),
  34.         AuthorizedFailures bigint,
  35.     )
  36.     INSERT INTO #Table2
  37.     SELECT TOP(@TopCount)
  38.         [UserName],
  39.         [AuthorizedFailures]
  40.     FROM #Table1
  41.     WHERE [UserName] != N'{[23124]}'
  42.     INSERT INTO #Table2
  43.     SELECT
  44.        N'{[23124]}' As UserName,
  45.         SUM(AuthorizedFailures)
  46.     FROM #Table1 t1
  47.     WHERE NOT EXISTS
  48.         (SELECT * FROM #Table2 t2
  49.          WHERE t1.UserName = t2.UserName)
  50.     DECLARE @TotalFailures bigint
  51.     SET @TotalFailures = (SELECT SUM(AuthorizedFailures) FROM #Table2)
  52.     SELECT
  53.         [dbo].[fnUserOrIpAddressToText]([UserName]) AS [UserName],
  54.         [AuthorizedFailures] AS AuthorizedFailures,
  55.         CASE
  56.             WHEN @TotalFailures = 0 THEN 0
  57.             ELSE (CAST(AuthorizedFailures AS decimal) / @TotalFailures)
  58.         END As AuthFailureRatio
  59.     FROM #Table2
  60. END
  61.