HseDMYy4

1SET NOCOUNT ON
2
3IF OBJECT_ID('tempdb..#Cases') IS NOT NULL
4	DROP TABLE #Cases
5
6IF OBJECT_ID('tempdb..#Users') IS NOT NULL
7	DROP TABLE #Users
8
9IF OBJECT_ID('tempdb..#Results') IS NOT NULL
10	DROP TABLE #Results
11
12
13CREATE TABLE #Cases (CPEID INT, CaseName VARCHAR(255), CaseDatabase VARCHAR(255))  
14
15CREATE TABLE #Results (CaseName VARCHAR(255), LastAccessedDate DATETIME, LastAccessedBy VARCHAR(255))  
16
17CREATE TABLE #Users (UserName VARCHAR(100))
18
19DECLARE @CPEID INT, @CaseName VARCHAR(255), @CaseDatabase VARCHAR(255), @SQL NVARCHAR(max), @Users NVARCHAR(255) 
20
21INSERT INTO #Cases
22SELECT CaseProductEnvironmentId, CPE.Name, DatabaseName
23FROM Enterprise.CaseProductEnvironment CPE
24INNER JOIN sys.databases D
25	ON D.name = CPE.DatabaseName
26WHERE ProductId = 3  
27
28INSERT INTO #Users
29SELECT Username
30FROM Membership.Users
31--Replace 'USERNAME1, USERNAME2 with true values.
32--WHERE Username IN ('USERNAME1', 'USERNAME2')
33
34WHILE EXISTS (
35		SELECT *
36		FROM #Cases
37		)
38BEGIN
39	SET @CPEID = (
40			SELECT TOP 1 CPEID
41			FROM #Cases
42			)
43	SET @CaseName = (
44			SELECT CaseName
45			FROM #Cases
46			WHERE CPEID = @CPEID
47			)
48	SET @CaseDatabase = (
49			SELECT CaseDatabase
50			FROM #Cases
51			WHERE CPEID = @CPEID
52			)  
53	SET @SQL = ' 
54
55USE ' + QUOTENAME(@CaseDatabase) + ' 
56
57INSERT INTO #Results 
58
59SELECT TOP 1 ''' + @CaseName + ''', 
60
61EventDateTime, 
62
63UserName  
64
65FROM ActivityTracking.F_UserSessionActivity USA 
66
67INNER JOIN ActivityTracking.DIM_User U ON U.UserKey = USA.UserKey
68
69WHERE U.Username IN (SELECT Username FROM #Users)
70
71ORDER BY EventDateTime DESC 
72
73'  
74
75	EXEC (@SQL)  
76
77	DELETE
78	FROM #Cases
79	WHERE CPEID = @CPEID
80END  
81
82SELECT *
83FROM #Results
84ORDER BY LastAccessedDate DESC  
85
86DROP TABLE #Results
87
88DROP TABLE #Cases
89
90DROP TABLE #Users