Queries

Update password/noreply emails

User Community last login

Recover a deleted survey

Restore data lost from activity retest

List of form elements for a specific survey

List of users who haven't logged in since XYZ

List of users who have completed a specific activity

Add a user to the PM team for a project

Updating password/noreply emails example:

UPDATE C
SET ccPasswordEmail = 'customer email here ',
ccNoReplyEmail = 'customer email here'
/*SELECT C.ccGeneralEmail,
C.ccPasswordEmail,
C.ccContactUsEmail,
C.ccNoReplyEmail,
C.ccSupportEmail*/
FROM tbl_Comm AS C

SELECT C.ccGeneralEmail AS GeneralEmail,
C.ccPasswordEmail,
C.ccContactUsEmail,
C.ccNoReplyEmail,
C.ccSupportEmail
FROM tbl_Comm AS C

Adding an admin back to a project example (2 options)

SELECT * FROM tbl_User

SELECT * FROM tbl_proj P where P.ccName = 'project name here'
SELECT * FROM tbl_team_level TL where ccProjID = 170 AND ccteamTypeID = 3
SELECT * FROM tbl_Team_Type
SELECT * FROM tbl_team T where ccteamID = 1682

UPDATE TU
SET ccStatusID = 1
--SELECT *
FROM tbl_team_User TU
where ccteamId = 1682 AND ccUserID = 16

UPDATE TU
SET ccStatusID = 1
--SELECT TU.*
FROM tbl_Proj P with (nolock)
INNER JOIN tbl_Team_Level TL with (nolock)
ON TL.ccProjID = P.ccProjID
INNER JOIN tbl_Team T with (nolock)
ON T.ccTeamID = TL.ccTeamID
INNER JOIN tbl_Team_User TU with (nolock)
ON TU.ccTeamID = T.ccTeamID
INNER JOIN tbl_User U with (nolock)
ON U.ccUserID = TU.ccUserID
WHERE P.ccProjID = 170
AND T.ccTeamID = 1682
AND U.ccUserID = 16

SELECT * FROM tbl_user where ccUser = 'Member1'

INSERT INTO tbl_Team_User (ccUserId, ccTeamId, ccStatusId, ccStatusDate, ccEntryDate)
VALUES (28, 1682, 1, GETDATE(), GETDATE())

/*
SELECT * FROM tbl_Proj P where ccProjID = 170
SELECT * FROM tbl_Team_level WHERE ccProjID = 170
*/

User Community last login example (2 options)

SELECT * FROM tbl_User_Login_Log

SELECT * FROM tbl_Sess

;WITH LastLogin AS (
SELECT S.ccUserID,
U.ccUser,
U.ccEmail,
MAX(ccEntryDate) AS LastLoginDate
FROM tbl_Sess S with (nolock)
INNER JOIN tbl_User U with (nolock)
ON U.ccUserID = S.ccUserID
WHERE S.ccSessionAuthenticationSourceType <> 'Administrative'
OR S.ccSessionAuthenticationSourceType IS NULL
GROUP BY
S.ccUserID,
U.ccUser,
U.ccEmail
)
SELECT *
FROM LastLogin
WHERE LastLoginDate < '2021-01-01'

SELECT S.ccUserID,
U.ccUser,
U.ccEmail,
MAX(ccEntryDate) AS LastLoginDate
FROM tbl_Sess S with (nolock)
INNER JOIN tbl_User U with (nolock)
ON U.ccUserID = S.ccUserID
WHERE S.ccSessionAuthenticationSourceType <> 'Administrative'
OR S.ccSessionAuthenticationSourceType IS NULL
GROUP BY
S.ccUserID,
U.ccUser,
U.ccEmail
HAVING
MAX(ccEntryDate) < '2021-01-01'

DECLARE
@intCAPID int,
@intLevel int,
@intUserID int = 16,
@datStartDate datetime = '1/1/1970'

SELECT 'Login' AS EventAreaType,
CASE
WHEN S.ccInitiatingIP IS NOT NULL THEN 'LoginLoggedIn'
ELSE 'LoginLoggedInNoIP'
END AS EventType,
S.ccInitiatingIP AS EventItemDetail,
S.ccEntryDate AS EventItemStatusDate
FROM tbl_Sess S with (nolock)
WHERE S.ccUserID = @intUserID
AND S.ccSessionAuthenticationSourceType <> 'Administrative'
AND S.ccEntryDate > @datStartDate

Recover a deleted survey example:

SELECT * FROM tbl_Proj

SELECT * FROM tbl_Form AS F
INNER JOIN tbl_Form_Level AS FL ON FL.ccFormID = F.ccFormID
WHERE FL.ccProjID =
AND F.ccFormTypeInstanceID =

SELECT * FROM tbl_Form_Elem WHERE ccFormID =

SELECT F.ccName, * FROM tbl_Form_Elem AS FE

INNER JOIN tbl_Form AS F ON FE.ccFormID = F.ccFormID

WHERE ccElemName like '%%'

Restore data lost from activity retest example:

SELECT * FROM tbl_Comm

SELECT * FROM tbl_Proj WHERE ccStatusID =

-- Content, Releases, Activites
SELECT * FROM tbl_Cont_Type_Level CTL WITH (NOLOCK)
INNER JOIN tbl_Cont_Type CT WITH (NOLOCK) ON CTL.ccContTypeID = CT.ccContTypeID
INNER JOIN tbl_Cont C WITH (NOLOCK) ON CT.ccContTypeID = C.ccContTypeID
WHERE CTL.ccProjID =
AND CT.ccNtcResTypeID =
AND C.ccStatusID =
AND CT.ccPhaseless =

SELECT * FROM tbl_Cont_Type_Level CTL WITH (NOLOCK)
INNER JOIN tbl_Cont_Type CT WITH (NOLOCK) ON CTL.ccContTypeID = CT.ccContTypeID
INNER JOIN tbl_Cont C WITH (NOLOCK) ON CT.ccContTypeID = C.ccContTypeID
WHERE C.ccPublicContID = ''

SELECT *
FROM tbl_Cont_User AS CU
WHERE CU.ccContID =

List of form elements for a specific survey example:

SELECT * FROM tbl_Proj
WHERE ccProjID =20
SELECT * FROM tbl_form WHERE ccStatusID =1 AND ccFormTypeInstanceID =1 AND ccFormID =110
SELECT * FROM tbl_Form_Elem WHERE ccFormID =110

List of users who haven't logged in since XYZ

WITH Lastlogin AS (
SELECT
S.ccUserID,
U.ccUser,
U.ccEmail,
MAX (S.ccEntryDate) AS LastLoginDate

FROM
tbl_Sess S WITH (NOLOCK)
INNER JOIN tbl_User U WITH (NOLOCK)
ON U.ccUserID = S.ccUserID AND ccStatusID <> 3
WHERE S.ccSessionAuthenticationSourceType <> 'Administrative'
OR S.ccSessionAuthenticationSourceType IS NULL
GROUP BY S.ccUserID, U.ccUser, U.ccEmail)
SELECT * FROM Lastlogin WHERE LastLoginDate > '2020-01-01'

List of users who have completed a specific activity example:

SELECT * FROM tbl_Comm
SELECT * FROM tbl_Proj WHERE ccStatusID =1 AND ccProjID=64
SELECT * FROM tbl_Cont_Type_Level AS CTL
INNER JOIN tbl_Cont_Type AS CT WITH (NOLOCK) ON CTL.ccContTypeID = CT.ccContTypeID WHERE CTL.ccContTypeID =649
SELECT * FROM tbl_Cont WHERE ccContID =813
SELECT * FROM tbl_Cont_User WHERE ccContID =813

Add a user to the PM team for a project example:

SELECT * FROM tbl_proj P where P.ccName = 'Quest Pro 3'

SELECT * FROM tbl_team_level TL where ccProjID =20 AND ccTeamTypeID =3

SELECT * FROM tbl_Team WHERE ccTeamID =119

SELECT * FROM tbl_Team_User WHERE ccTeamID =119

SELECT * FROM tbl_User

INSERT INTO tbl_Team_User (ccTeamID, ccUserID)

VALUES (119, 46)

Notes:

How to pull status ID + descriptions

SELECT * FROM tbl_Status

ccStatusID=

Published/Active
Retracted/Deactivated
Removed/Deleted
Creating/Pending (Active)
Rejected/Denied
Historic Archive
Building/Processing

<> = IS NOT (Example: WHERE S.ccSessionAuthenticationSourceType <> 'Administrative’ - This means we are looking for session source types that ARE NOT admins)
IS NULL = before C17