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