Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?-- =============================================
-- Description: Returns detail data for from web logs for reporting.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [dbo].[uspRptDetWebLogs]
-- Add the parameters for the stored procedure here
@minRequestDate datetime,
@maxRequestDate datetime,
@adDomains varchar(max),
@actionTypes varchar(max),
@requestPages varchar(max),
@userNames varchar(max),
@userId int

AS
BEGIN
DECLARE @tmpMaxDate datetime
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
--to make sure the full day has been returned, add a day to the maxRequestDate
IF @maxRequestDate Is Not Null
BEGIN
SET @tmpMaxDate = DATEADD(day, 1, @maxRequestDate);
END
--make sure the max date is not less than the min date
IF @tmpMaxDate <= @minRequestDate
BEGIN
SET @tmpMaxDate = DATEADD(day, 1, @minRequestDate);
END

SELECT
WebLogs.ADDomain
, WebLogs.UserName
, RequestDate
, RemoteAddress
, RequestPage
, ActionType
, RecordID
, WebMessage
, seclyr.Users.EmailAddress
, (seclyr.Users.LastName + ', ' + seclyr.Users.FirstName) AS FullName
FROM WebLogs
INNER JOIN seclyr.Users ON seclyr.Users.UserName = WebLogs.UserName
WHERE (RequestDate >= @minRequestDate OR @minRequestDate IS NULL)
AND (RequestDate <= @tmpMaxDate OR @tmpMaxDate IS NULL)
AND (WebLogs.ADDomain In(SELECT [Value] FROM ufnSplit(',', @adDomains)) OR @adDomains IS NULL)
AND (ActionType In(SELECT [Value] FROM ufnSplit(',', @actionTypes)) OR @actionTypes IS NULL)
AND (RequestPage In(SELECT [Value] FROM ufnSplit(',', @requestPages)) OR @requestPages IS NULL)
AND (WebLogs.UserName In (SELECT [Value] FROM ufnSplit(',', @userNames)) OR @userNames IS NULL)
ORDER BY RequestDate DESC
;
END TRY

BEGIN CATCH
PRINT 'There was an error processing the detail web logs report' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH

--DECLARE @webMessage varchar(MAX)
--SET @webMessage= 'Date Range: ' + CONVERT(varchar(12), @minRequestDate, 120 ) + ' - ' + CONVERT(varchar(12), @maxRequestDate, 120 ) + ', ADDomains Selected: ' + @adDomains + ', Requested Pages: ' + @requestPages + ', Selected Users: ' + @userName
--EXEC uspSaveRptParameters @userId, 'uspRptWebLogDetails', @webMessage
END
GO