Jump to content

SQL - List all Eclipse cases and when they were last accessed


Andrew Coody

Recommended Posts

  • IPRO Employee

Warnings

One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.


If Nucleus (ADD) and Eclipse databases are on the same SQL instance, run Query # 1.

If Nucleus (ADD) and Eclipse databases are not on the same SQL instance, run Query # 2.*

*The servers must be linked. [1]

 

 

 

Query # 1

 

/***********************************************************
Name: LastAccessedDateForCases.sql
Author: Levi Roble, Ipro Tech LLC
Date: 9/6/2016
Use: Run this on the Nucleus database to get a list of all
Eclipse Cases and when they were last accessed and what
User last accessed them
Notes: This query will not work if Nucleus and Eclipse are
on different SQL instances
***********************************************************/
 
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Cases') IS NOT NULL DROP TABLE #Cases
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
 
CREATE TABLE #Cases
(
CPEID int,
CaseName varchar(max),
CaseDatabase varchar(max)
)
 
CREATE TABLE #Results
(
CaseName varchar(max),
LastAccessedDate datetime,
LastAccessedBy varchar(max)
)
 
DECLARE @CPEID int,@CaseName varchar(max),@CaseDatabase varchar(max),@SQL nvarchar(max)
 
INSERT INTO #Cases
SELECT CaseProductEnvironmentId,CPE.Name,DatabaseName FROM Enterprise.CaseProductEnvironment CPE
INNER JOIN sys.databases D ON D.name = CPE.DatabaseName
WHERE ProductId = 3
 
WHILE EXISTS(SELECT * FROM #Cases)
BEGIN
SET @CPEID = (SELECT TOP 1 CPEID FROM #Cases)
SET @CaseName = (SELECT CaseName FROM #Cases WHERE CPEID = @CPEID)
SET @CaseDatabase = (SELECT CaseDatabase FROM #Cases WHERE CPEID = @CPEID)
 
SET @SQL = '
USE ' + QUOTENAME(@CaseDatabase) + '
INSERT INTO #Results
SELECT TOP 1 ''' + @CaseName + ''',EventDateTime,UserName FROM ActivityTracking.F_UserSessionActivity USA
INNER JOIN ActivityTracking.DIM_User U ON U.UserKey = USA.UserKey
ORDER BY EventDateTime DESC
'
 
EXEC (@SQL)
 
DELETE FROM #Cases WHERE CPEID = @CPEID
END
 
SELECT * FROM #Results
ORDER BY LastAccessedDate DESC
 
DROP TABLE #Results
DROP TABLE #Cases

 

 

 

 

Query # 2

 

/***********************************************************
Original Author: Levi Roble, Ipro Tech LLC
Modified Author(s): Cody Luera & Andrew Coody, Ipro Tech LLC
Date: 8/22/2017
Use: Run this on the Nucleus database to get a list of all
Eclipse Cases and when they were last accessed and what
User last accessed them
Notes: This query will work if Nucleus and Eclipse are
on different SQL instances.
* You must replace [iNSTANCE_NAME] with the name
of the other linked SQL server.
* You must supply the email address (username) of the
super admins to exclude. You can alternately return
the super admins by commenting out the WHERE
clause block that contains email addresses.
***********************************************************/
 
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Cases') IS NOT NULL DROP TABLE #Cases
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
 
CREATE TABLE #Cases
(
CPEID int,
CaseName varchar(max),
CaseDatabase varchar(max)
)
 
CREATE TABLE #Results
(
CaseName varchar(max),
LastAccessedDate datetime,
LastAccessedBy varchar(max)
)
 
DECLARE @CPEID int,@CaseName varchar(max),@CaseDatabase varchar(max),@SQL nvarchar(max)
 
INSERT INTO #Cases
SELECT CaseProductEnvironmentId,CPE.Name,DatabaseName FROM Enterprise.CaseProductEnvironment CPE
INNER JOIN [iNSTANCE_NAME].master.sys.databases D ON D.name = CPE.DatabaseName
WHERE ProductId = 3 AND DatabaseName not like 'Eclipse_Config_Case002650'
 
WHILE EXISTS(SELECT * FROM #Cases)
BEGIN
SET @CPEID = (SELECT TOP 1 CPEID FROM #Cases)
SET @CaseName = (SELECT CaseName FROM #Cases WHERE CPEID = @CPEID)
SET @CaseDatabase = (SELECT CaseDatabase FROM #Cases WHERE CPEID = @CPEID)
 
SET @SQL = '
INSERT INTO #Results
SELECT TOP 1 ''' + @CaseName + ''',EventDateTime,UserName FROM [iNSTANCE_NAME].' + @CaseDatabase + '.ActivityTracking.F_UserSessionActivity USA
INNER JOIN [iNSTANCE_NAME].' + @CaseDatabase + '.ActivityTracking.DIM_User U ON U.UserKey = USA.UserKey
-- /*
where username not in (
 ''administrator@iprotech.com'',
   ''email1@domain.com'',
 ''email2@domain.com'',
 ''email3@domain.com''
)
-- */

ORDER BY EventDateTime DESC
'
 
EXEC (@SQL)
 
DELETE FROM #Cases WHERE CPEID = @CPEID
END
 
SELECT * FROM #Results
ORDER BY LastAccessedDate DESC
 
DROP TABLE #Results
DROP TABLE #Cases

 

 


[1] "Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel." (SOURCE)
Link to comment
Share on other sites

  • IPRO Employee

Warnings

One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.


If Nucleus (ADD) and Eclipse databases are on the same SQL instance, run Query # 1.

If Nucleus (ADD) and Eclipse databases are not on the same SQL instance, run Query # 2.*

*The servers must be linked. [1]

 

 

 

Query # 1

 

/***********************************************************
Name: LastAccessedDateForCases.sql
Author: Levi Roble, Ipro Tech LLC
Date: 9/6/2016
Use: Run this on the Nucleus database to get a list of all
Eclipse Cases and when they were last accessed and what
User last accessed them
Notes: This query will not work if Nucleus and Eclipse are
on different SQL instances
***********************************************************/
 
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Cases') IS NOT NULL DROP TABLE #Cases
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
 
CREATE TABLE #Cases
(
CPEID int,
CaseName varchar(max),
CaseDatabase varchar(max)
)
 
CREATE TABLE #Results
(
CaseName varchar(max),
LastAccessedDate datetime,
LastAccessedBy varchar(max)
)
 
DECLARE @CPEID int,@CaseName varchar(max),@CaseDatabase varchar(max),@SQL nvarchar(max)
 
INSERT INTO #Cases
SELECT CaseProductEnvironmentId,CPE.Name,DatabaseName FROM Enterprise.CaseProductEnvironment CPE
INNER JOIN sys.databases D ON D.name = CPE.DatabaseName
WHERE ProductId = 3
 
WHILE EXISTS(SELECT * FROM #Cases)
BEGIN
SET @CPEID = (SELECT TOP 1 CPEID FROM #Cases)
SET @CaseName = (SELECT CaseName FROM #Cases WHERE CPEID = @CPEID)
SET @CaseDatabase = (SELECT CaseDatabase FROM #Cases WHERE CPEID = @CPEID)
 
SET @SQL = '
USE ' + QUOTENAME(@CaseDatabase) + '
INSERT INTO #Results
SELECT TOP 1 ''' + @CaseName + ''',EventDateTime,UserName FROM ActivityTracking.F_UserSessionActivity USA
INNER JOIN ActivityTracking.DIM_User U ON U.UserKey = USA.UserKey
ORDER BY EventDateTime DESC
'
 
EXEC (@SQL)
 
DELETE FROM #Cases WHERE CPEID = @CPEID
END
 
SELECT * FROM #Results
ORDER BY LastAccessedDate DESC
 
DROP TABLE #Results
DROP TABLE #Cases

 

 

 

 

Query # 2

 

/***********************************************************
Original Author: Levi Roble, Ipro Tech LLC
Modified Author(s): Cody Luera & Andrew Coody, Ipro Tech LLC
Date: 8/22/2017
Use: Run this on the Nucleus database to get a list of all
Eclipse Cases and when they were last accessed and what
User last accessed them
Notes: This query will work if Nucleus and Eclipse are
on different SQL instances.
* You must replace [iNSTANCE_NAME] with the name
of the other linked SQL server.
* You must supply the email address (username) of the
super admins to exclude. You can alternately return
the super admins by commenting out the WHERE
clause block that contains email addresses.
***********************************************************/
 
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Cases') IS NOT NULL DROP TABLE #Cases
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
 
CREATE TABLE #Cases
(
CPEID int,
CaseName varchar(max),
CaseDatabase varchar(max)
)
 
CREATE TABLE #Results
(
CaseName varchar(max),
LastAccessedDate datetime,
LastAccessedBy varchar(max)
)
 
DECLARE @CPEID int,@CaseName varchar(max),@CaseDatabase varchar(max),@SQL nvarchar(max)
 
INSERT INTO #Cases
SELECT CaseProductEnvironmentId,CPE.Name,DatabaseName FROM Enterprise.CaseProductEnvironment CPE
INNER JOIN [iNSTANCE_NAME].master.sys.databases D ON D.name = CPE.DatabaseName
WHERE ProductId = 3 AND DatabaseName not like 'Eclipse_Config_Case002650'
 
WHILE EXISTS(SELECT * FROM #Cases)
BEGIN
SET @CPEID = (SELECT TOP 1 CPEID FROM #Cases)
SET @CaseName = (SELECT CaseName FROM #Cases WHERE CPEID = @CPEID)
SET @CaseDatabase = (SELECT CaseDatabase FROM #Cases WHERE CPEID = @CPEID)
 
SET @SQL = '
INSERT INTO #Results
SELECT TOP 1 ''' + @CaseName + ''',EventDateTime,UserName FROM [iNSTANCE_NAME].' + @CaseDatabase + '.ActivityTracking.F_UserSessionActivity USA
INNER JOIN [iNSTANCE_NAME].' + @CaseDatabase + '.ActivityTracking.DIM_User U ON U.UserKey = USA.UserKey
-- /*
where username not in (
 ''administrator@iprotech.com'',
   ''email1@domain.com'',
 ''email2@domain.com'',
 ''email3@domain.com''
)
-- */

ORDER BY EventDateTime DESC
'
 
EXEC (@SQL)
 
DELETE FROM #Cases WHERE CPEID = @CPEID
END
 
SELECT * FROM #Results
ORDER BY LastAccessedDate DESC
 
DROP TABLE #Results
DROP TABLE #Cases

 

 


[1] "Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel." (SOURCE)
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...