Alrighty. We’re all data people here so you know we all agree that knowledge is power. One thing that is absolutely helpful in a large corporate environment is knowing if someone has been let go/laid off/fired. Using linked server to query active directory you can fairly easily (especially if someone else wrote the query) see whose accounts are disabled and which accounts have an expiration date. Obviously, these two things don’t guarantee someone is no longer with the company (or that their last day is coming up) but they can be pretty good indicators. The final result set on this query is named ‘The Goodbye List”.
Remember: this is sensitive information and while knowledge is power, with great power comes great responsibility.
IF OBJECT_ID('tempdb..#UserAccountControl') IS NOT NULL BEGIN DROP TABLE #UserAccountControl END CREATE TABLE #UserAccountControl ( UserAccountControlValue INT , UserAccountControlDescription VARCHAR(1000) ) INSERT #UserAccountControl ( UserAccountControlValue, UserAccountControlDescription ) VALUES ( '512', 'Enabled Account' ), ( '514', 'Disabled Account' ), ( '544', 'Enabled, Password Not REQUIRED' ), ( '546', 'Disabled, Password Not REQUIRED' ), ( '66048', 'Enabled, Password Doesn''t Expire' ), ( '66050', 'Disabled, Password Doesn''t Expire' ), ( '66080', 'Enabled, Password Doesn''t Expire & Not Required' ), ( '66082', 'Disabled, Password Doesn''t Expire & Not Required' ), ( '262656', 'Enabled, Smartcard REQUIRED' ), ( '262658', 'Disabled, Smartcard Required' ), ( '262688', 'Enabled, Smartcard Required, Password Not REQUIRED' ), ( '262690', 'Disabled, Smartcard Required, Password Not Required' ), ( '328192', 'Enabled, Smartcard Required, Password Doesn''t Expire' ), ( '328194', 'Disabled, Smartcard Required, Password Doesn''t Expire' ), ( '328224', 'Enabled, Smartcard Required, Password Doesn''t Expire & Not Required' ), ( '328226', 'Disabled, Smartcard Required, Password Doesn''t Expire & Not Required' ) IF OBJECT_ID('tempdb..#tmpAD') IS NOT NULL BEGIN DROP TABLE #tmpAD END SELECT samAccountName AS WinNT_ID , sn AS Last_Name , givenName AS First_Name , displayName AS Display_Name , ISNULL(title, 'N/A') AS Job_Title , ISNULL(department, 'N/A') AS Department , l AS City , userAccountControl , accountExpires INTO #tmpAD FROM OPENQUERY(YourActiveDirectoryLinkedServerName, ' SELECT samAccountName , sn , givenName , title , displayName , department , mail , telephonenumber , mobile , facsimiletelephonenumber , physicalDeliveryOfficeName , l , streetAddress , st , postalCode , userAccountControl , accountExpires FROM ''LDAP:// DC=brandes,DC=com'' WHERE objectClass=''user'' AND objectClass<>''computer'' ') AS tblADSI WHERE sn IS NOT NULL --the goodbye list SELECT CASE WHEN ad.accountexpires IN ( '9223372036854775807' , '0' , '129895740000000000' ) THEN '9999-12-31' ELSE DATEADD(mi, ( CAST(ad.accountExpires AS BIGINT) / 600000000 ) + DATEDIFF(Minute, GETUTCDATE(), GETDATE()), CAST('1/1/1601' AS DATETIME2)) END AS AccountExpiresDate , uac.UserAccountControlDescription , ad.Display_Name , ad.Department , ad.Job_Title , ad.WinNT_ID , ad.accountexpires FROM #tmpAD AS ad LEFT JOIN #UserAccountControl AS uac ON ad.useraccountcontrol = uac.UserAccountControlValue WHERE ad.WinNT_ID NOT LIKE '%[0-9]%' --eliminate temps by eliminating logins with a number in them AND ( CASE WHEN ad.accountexpires IN ( '9223372036854775807', '0', '129895740000000000' ) THEN '9999-12-31' ELSE DATEADD(mi, ( CAST(ad.accountExpires AS BIGINT) / 600000000 ) + DATEDIFF(Minute, GETUTCDATE(), GETDATE()), CAST('1/1/1601' AS DATETIME2)) END <= GETDATE() + 14 OR UserAccountControlDescription LIKE '%disabled%' ) ORDER BY 1 DESC
One response to “SQL Query To Find Expired Accounts In Active Directory: Knowledge Is Power And The Goodbye List”
Been searching all over for the conversion of accountExpires to datetime. Thanks!