Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/15/2024 05:14 AM
Hi Team,
We have a requirement to get the list of Okta accounts that have been last logged in 45 days back for which we were able to provide the data using the below query.
SELECT U.USERNAME AS 'Employee Number', U.FIRSTNAME AS 'USER FIRST NAME', U.LASTNAME AS 'USER LAST NAME', A.DISPLAYNAME AS 'Display Name', U.EMAIL AS 'User EMAIL', A.CUSTOMPROPERTY19 AS 'Manager', U.TITLE AS 'Title', U.EMPLOYEETYPE AS 'Employee Type', DATE_FORMAT(A.CUSTOMPROPERTY12,'%M %d, %Y %H:%i:%s') AS 'OKTALASTLOGIN', A.CUSTOMPROPERTY6 AS 'Okta Status', case when u.customproperty40=1 then 'LegalHoldUser' else 'NonLegalHoldUser' end as LegalHoldStatus,A.CUSTOMPROPERTY53 AS 'providerType', A.CUSTOMPROPERTY54 AS 'providerName', DATE_FORMAT(U.ENDDATE, '%M %d, %Y 00:00:00') AS 'last_day_of_work', DATE_FORMAT(U.TERMDATE , '%M %d, %Y 00:00:00') AS 'Term_Dte', DATE_FORMAT(U.CUSTOMPROPERTY7, '%M %d, %Y 00:00:00') AS 'Data Retention Date', DATE_FORMAT(U.STARTDATE, '%M %d, %Y 00:00:00') AS 'Start Date' FROM ACCOUNTS A,USER_ACCOUNTS UA, USERS U WHERE A.ACCOUNTKEY=UA.ACCOUNTKEY and UA.USERKEY=U.USERKEY AND A.ENDPOINTKEY = 6 AND A.CUSTOMPROPERTY6 IN ('Active') AND A.CUSTOMPROPERTY12 <= DATE_SUB(CURDATE(), INTERVAL 45 DAY) and U.STARTDATE <= DATE_SUB(CURDATE(), INTERVAL 45 DAY)
We have an added requirement to get these user's AD status too in the same report. Can we have the details of both Okta and AD status displayed in the same query and if yes, how can I achieve it?
Solved! Go to Solution.
04/15/2024 09:01 AM
@SowmithriV : Use below query and replace AD endpoint key with respective value
SELECT U.USERNAME AS 'Employee Number',
U.FIRSTNAME AS 'USER FIRST NAME',
U.LASTNAME AS 'USER LAST NAME',
A.DISPLAYNAME AS 'Display Name',
U.EMAIL AS 'User EMAIL',
A.CUSTOMPROPERTY19 AS 'Manager',
U.TITLE AS 'Title',
U.EMPLOYEETYPE AS 'Employee Type',
DATE_FORMAT(A.CUSTOMPROPERTY12, '%M %d, %Y %H:%i:%s') AS 'OKTALASTLOGIN',
A.CUSTOMPROPERTY6 AS 'Okta Status',
case
when u.customproperty40=1 then 'LegalHoldUser'
else 'NonLegalHoldUser'
end as LegalHoldStatus,
A.CUSTOMPROPERTY53 AS 'providerType',
A.CUSTOMPROPERTY54 AS 'providerName',
DATE_FORMAT(U.ENDDATE, '%M %d, %Y 00:00:00') AS 'last_day_of_work',
DATE_FORMAT(U.TERMDATE, '%M %d, %Y 00:00:00') AS 'Term_Dte',
DATE_FORMAT(U.CUSTOMPROPERTY7, '%M %d, %Y 00:00:00') AS 'Data Retention Date',
DATE_FORMAT(U.STARTDATE, '%M %d, %Y 00:00:00') AS 'Start Date',
AD.NAME AS AD_ACCOUNT_NAME,
AD.STATUS AS AD_ACCOUNT_STATUS
FROM ACCOUNTS A
INNER JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY=UA.ACCOUNTKEY
INNER JOIN USERS U ON UA.USERKEY=U.USERKEY
INNER JOIN USER_ACCOUNTS UAD ON U.USERKEY=UAD.USERKEY
INNER JOIN ACCOUNTS AD ON UAD.ACCOUNTKEY=AD.ACCOUNTKEY
WHERE A.ENDPOINTKEY = 6
AND A.CUSTOMPROPERTY6 IN ('Active')
AND A.CUSTOMPROPERTY12 <= DATE_SUB(CURDATE(), INTERVAL 45 DAY)
AND U.STARTDATE <= DATE_SUB(CURDATE(), INTERVAL 45 DAY)
AND AD.ENDPOINTKEY = <AD Endpoint Key>
04/15/2024 07:25 PM
SELECT U.username AS
'Employee Number',
U.firstname AS
'USER FIRST NAME',
U.lastname AS 'USER LAST NAME'
,
A.displayname AS
'Display Name',
U.email AS 'User EMAIL',
A.customproperty19 AS 'Manager',
U.title AS 'Title',
U.employeetype AS 'Employee Type',
Date_format(A.customproperty12, '%M %d, %Y %H:%i:%s') AS 'OKTALASTLOGIN',
A.customproperty6 AS 'Okta Status',
CASE
WHEN u.customproperty40 = 1 THEN 'LegalHoldUser'
ELSE 'NonLegalHoldUser'
END AS LegalHoldStatus,
A.customproperty53 AS 'providerType',
A.customproperty54 AS 'providerName',
Date_format(U.enddate, '%M %d, %Y 00:00:00') AS
'last_day_of_work',
Date_format(U.termdate, '%M %d, %Y 00:00:00') AS 'Term_Dte',
Date_format(U.customproperty7, '%M %d, %Y 00:00:00') AS
'Data Retention Date',
Date_format(U.startdate, '%M %d, %Y 00:00:00') AS 'Start Date',
(SELECT status
FROM accounts ad
WHERE ad.endpointkey = 1
AND ad.name = a.name) AS
'AD Account Status'
FROM accounts A,
user_accounts UA,
users U
WHERE A.accountkey = UA.accountkey
AND UA.userkey = U.userkey
AND A.endpointkey = 6
AND A.customproperty6 IN ( 'Active' )
AND A.customproperty12 <= Date_sub(Curdate(), interval 45 day)
AND U.startdate <= Date_sub(Curdate(), interval 45 day)
Update bold part