May 5, 2016 at 11:33 am
I'll try not to confuse anyone.
I'm trying to retrieve a list of users in a application that doesn't exist in Active Directory so we can purge the app users. The application login name is an email address. The part before the @ is the account name that exists in Active Directory. I have that part working. My issue is some of the login names in the app has the number from their CAC card as the email (for example 123456@mail.com) which is not the actual username that's exists in Active Directory. So the query returns the accounts with the number as the email address but in reality they do exist. Is there a way to get the users with the number as the email address to show if it doesn't exist in AD?
Here is my query
select substring(dbo.login_name,1,charindex('@',dbo.login_name+'@')-1) as ADUSERID, dbo.full_name, dbo.ADAccountName
left outer join dbo.ADObj
on substring(dbo.login_name,1,charindex('@',dbo.login_name+'@')-1) = dbo.ADAccountName
Hope this isn't confusing.
Thanks
May 5, 2016 at 12:11 pm
is the CAC card number in table anyplace, or can it be used to get CACNumber +email, so it can be joined to your ADObj table?
your snippet was incomplete/not syntactically correct, but you might be able to use a case statement to join the data?
SELECT * FROM T1
INNER JOIN SomeTableWithCAC C1 ON T1.Something = C1.Something --CAC# to login reference?
left outer join dbo.ADObj T2
on CASE
WHEN IsNumeric(substring(T1.login_name,1,charindex('@',T1.login_name+'@')-1)) = 1
THEN C1.LoginName
ELSE substring(T1.login_name,1,charindex('@',T1.login_name+'@')-1)
END = T2.ADAccountName
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply