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 user login activity.
-- =============================================
CREATE PROCEDURE [seclyr].[uspRptDetUserLoginActivity]
@adDomains varchar(max),
@moduleIds varchar(max),
@userId int

AS
DECLARE @stationsTable TABLE
(
RowID int,
ModuleID tinyint,
RegionID tinyint, --this is VHA Region
CCNRegionID tinyint, --this is CCN Region
VisnID tinyint,
StationID smallint,
ParentStationID smallint,
Station3N varchar(10),
Station6A varchar(10),
StationDisplayName varchar(100),
StationName varchar(100),
IsParent bit,
Inactive bit
)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
--populate what stations the user has permission to see
INSERT INTO @stationsTable (RowID, ModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID, Station3N, Station6A
, StationDisplayName, StationName, IsParent, Inactive)
EXEC seclyr.uspGetUserParentStations @userId, @moduleIds;

SELECT DISTINCT
ADDomain
, LastName
, FirstName
, RoleName
, AccessLevelName
, seclyr.UserRoles.RegionID
, AssignedRegions.RegionCode
, seclyr.UserRoles.CCNRegionID
, AssignedCCNRegions.RegionCode AS 'CCNRegionCode'
, seclyr.UserRoles.VisnID
, AssignedVisns.VisnCode
, seclyr.UserRoles.StationID
, Stations.Station3N
, Stations.StationDisplayName
, UserName
, EmailAddress
, DateLastLogin
, seclyr.Users.DateInactive
, seclyr.Users.Inactive

FROM seclyr.UserRoles
INNER JOIN seclyr.Users ON seclyr.Users.UserID = seclyr.UserRoles.UserID
INNER JOIN seclyr.Roles ON seclyr.Roles.RoleID = seclyr.UserRoles.RoleID
INNER JOIN seclyr.AccessLevels ON seclyr.AccessLevels.AccessLevelID = seclyr.UserRoles.AccessLevelID
INNER JOIN seclyr.Modules ON seclyr.Modules.ModuleID = seclyr.UserRoles.ModuleID
LEFT OUTER JOIN @stationsTable Stations ON seclyr.UserRoles.StationID = Stations.StationID
LEFT OUTER JOIN Visns AssignedVisns ON seclyr.UserRoles.VisnID = AssignedVisns.VisnID
LEFT OUTER JOIN Regions AssignedRegions ON seclyr.UserRoles.RegionID = AssignedRegions.RegionID
LEFT OUTER JOIN Regions AssignedCCNRegions ON seclyr.UserRoles.CCNRegionID = AssignedRegions.RegionID
WHERE
(ADDomain IN(SELECT [Value] FROM ufnSplit(',', @adDomains)) OR @adDomains IS NULL)
AND (seclyr.UserRoles.ModuleID IN(SELECT [Value] FROM ufnSplit(',', @moduleIds)) OR @moduleIds IS NULL)
ORDER BY ADDomain, seclyr.UserRoles.VisnID, seclyr.UserRoles.StationID, LastName, FirstName
;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the User Login Activity' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH

--DECLARE @webMessage varchar(MAX)
--SET @webMessage= 'ADDomains Selected: ' + @adDomains + ' for Modules: ' + @moduleIds
--EXEC uspSaveRptParameters @userId, 'uspRptUserLoginActivity', @webMessage

END