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 a list of weblog data for table data and graph visualizations
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [dbo].[uspRptVizWebLogUserRoles]
-- Add the parameters for the stored procedure here
@requestDate date
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY

SELECT
[Date],
ISNULL (SUM(DistinctUsers), 0) AS DistinctUsers,
ISNULL (SUM(Distinctusers9to5), 0) AS DistinctUsers9to5,
ISNULL (SUM(ApprovedDistinctUsers), 0) AS ApprovedDistinctUsers,
ISNULL (SUM(RequestedDistinctUsers), 0) AS RequestedDistinctUsers
FROM (
--get the users per date = @requestDate parameter
SELECT
CAST(RequestDate AS Date) AS [Date]
, COUNT(DISTINCT UserName) AS DistinctUsers
, NULL AS DistinctUsers9to5
, NULL AS ApprovedDistinctUsers
, NULL AS RequestedDistinctUsers
FROM WebLogs
WHERE RequestDate >= @requestDate
AND RequestDate < DATEADD(day, 1, @requestDate)
GROUP BY CAST(RequestDate AS Date)

UNION ALL
--get distinct users within an hour timeframe
SELECT CAST(RequestDate AS Date) AS [Date]
, NULL AS DistinctUsers
, COUNT(DISTINCT UserName) AS DistinctUsers9to5
, NULL AS ApprovedDistinctUsers
, NULL AS RequestedDistinctUsers
FROM WebLogs
WHERE RequestDate >= @requestDate
AND RequestDate < DATEADD(day, 1, @requestDate)
AND DATEPART(hour, requestdate) Between 9 And 17 --filter out anything before 9am and after 5pm
GROUP BY CAST(RequestDate AS Date)

UNION ALL
--get total Approved users
SELECT @requestDate AS [Date]
, NULL AS DistinctUsers
, NULL AS DistinctUsers9to5
, COUNT(Distinct UserID) AS ApprovedDistinctUsers
, NULL AS RequestedDistinctUsers
FROM seclyr.UserRoles
WHERE AccessStatus = 'APPROVED'

UNION ALL
--get total requested users
SELECT @requestDate AS [Date]
, NULL AS DistinctUsers
, NULL AS DistinctUsers9to5
, NULL AS ApprovedDistinctUsers
, COUNT(Distinct UserID) AS RequestedDistinctUsers
FROM seclyr.UserRoles
WHERE AccessStatus = 'REQUESTED'

) AS Activities
GROUP BY Activities.[Date]
;
END TRY

BEGIN CATCH
PRINT 'There was an error processing the web log and table data visuals' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END