SQL Query to get Okta and AD status

[SowmithriV]

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?

[Saathvik · Accepted solution]

@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>

[rushikeshvartak]

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