0

I have a table which has data from Active Directory. It has 3 columns; namely: Machine Name, DistinguishedName and ObjectPath. For Example:

a. Machine Name: WKS123BBB
b. Distinguished Name: CN=WKS123BBB,OU=Workstations,OU=GB,OU=Countries,DC=A,DC=B,DC=C,DC=com
c. ObjectPath: LDAP://a.b.c.com,CN=WKS123BBB,OU=Workstations,OU=GB,OU=Countries,DC=A,DC=B,DC=C,DC=com

Below the domain we have Countries OU, below this OU we have Country Code (US), below Country code we have 3 OU's for Workstations. There are 18 Countries.

I am trying to create a view in SQL SERVER with the below column:

a. MachineName
b. OU
c. CountryCode

Note: There are 3 OU's below the Country code: Workstations, Workstations_Indus and WorkstationsDisabled

Can someone please help how to extract the Country Code and OU from Distinguished Name column. For example it should be like:

a. MachineName = WKS123BBB
b. OU = Workstations/Workstations_Indus/WorkstationsDisabled
c. CountryCode = GB

from CN=WKS123BBB,OU=Workstations,OU=GB,OU=Countries,DC=A,DC=B,DC=C,DC=com

Thanks in advance

Improve this question
6
  • An idea would be to create a CLR assembly that called RegEx.Find method in .NET framework.
    – bjnr
    Commented Dec 4, 2013 at 11:50
  • Thanks, I want to do this using a SQL Query. May be use an in-built function, not sure....
    – Rajiv
    Commented Dec 4, 2013 at 11:58
  • @Rajiv See this link.. may it will help to you.. stackoverflow.com/questions/2647/split-string-in-sql you can use PATINDEX to split by "," and then find "OU" from the current substring..
    – Vishal Patel
    Commented Dec 4, 2013 at 12:02
  • Is distinguished name always in the same format? - Perhaps you could run a SELECT TOP 5 DistinguishedName FROM [yourtable] to get us some more test data.
    – Mack
    Commented Dec 4, 2013 at 12:02
  • @Mack: Yes, its the format how Active Directory stores.
    – Rajiv
    Commented Dec 4, 2013 at 12:12

1 Answer 1

Reset to default
1

This looks a bit horrible but works. The input string has to have CN=XXX, and then two times OU=XXX, for it to work.

This should work ok in a view as it uses only deterministic CHARINDEX and SUBSTRING functions.

declare @s as varchar(200) = 'CN=WKS123BBB,OU=Workstations,OU=GB,OU=Countries,DC=A,DC=B,DC=C,DC=com'

select SUBSTRING(@s, CHARINDEX('CN=', @s, 1) + 3, CHARINDEX(',', @s, CHARINDEX('CN=', @s, 1) + 2) - 4) as MachineName,
    SUBSTRING(@s, CHARINDEX(',OU=', @s, 1) + 4, CHARINDEX(',', @s, CHARINDEX(',OU=', @s, 1) + 1) - CHARINDEX(',OU=', @s, 1) - 4) as OU,
    SUBSTRING(@s, CHARINDEX(',OU=', @s, CHARINDEX(',OU=', @s, 1) + 1) + 4, CHARINDEX(',', @s, CHARINDEX(',OU=', @s, CHARINDEX(',OU=', @s, 1) + 1) + 1) -CHARINDEX(',OU=', @s, CHARINDEX(',OU=', @s, 1) + 1) - 4) as CountryCode
Improve this answer
4
  • Thanks, it works. Unfortunately I came to know that are 2 more OU's under Country code, which make 3 in total which are static across all Countries. Workstations, Workstations_Indus and WorkstationsDisabled. Could you please help for this too.... Thanks a lot
    – Rajiv
    Commented Dec 4, 2013 at 12:29
  • 2
    Rather than asking @Szymon to it all for you, wouldn't it be better fo you to work through the above example and learn how to do it yourself.
    – Mack
    Commented Dec 4, 2013 at 13:30
  • @Rajiv It will work for 2 more parts but will become rather horrible. See the pattern, because of the repeating OU=, there has to be charindex within charindex. But you can give our a go.
    – Szymon
    Commented Dec 4, 2013 at 18:48
  • @Syzmon: Thank you very much for your advice. It really helped me in learning something new. Thanks :)
    – Rajiv
    Commented Dec 6, 2013 at 7:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.