A lot of times I’m asked to provide the location in Active Directory (Organizational Unit, Container) in a report. I typically use the v_ra_system_systemouname and v_ra_system_systemcontainername by selecting the longest string for each ResourceID, but it’s kind of kludgy.
I found a post on the archived forums that had some code to create a function, but it was limited in that it expects a two-part domain name, such as DC=domain,DC=name. Passing anything different (such as DC=domain,DC=name,DC=local) returns incorrect results.
Based on the post, I was able generalize it and create my own [this uses the string_split function, which is in SQL Server 2016 (13.x) and later]:
create function [dbo].[GetADLocationFromDN] (
@InputString varchar(max)
)
returns varchar(max)
begin
/*
Given an distinguished name input string:
'CN=XYZ123PW5OASDC,OU=SiteA,OU=Domain Controllers,DC=abc,DC=def,DC=ghi,DC=jkl,DC=com'
we want to return the Active Directory Location:
'ABC.DEF.GHI.JKL.COM/DOMAIN CONTROLLERS/SITEA'
*/
declare @WorkString varchar (max) = ''
declare @Rows int
declare @Counter int = 1
declare @SplitItems table (
RowNumber int identity (1, 1),
ParsedValue varchar(max)
)
/*
Validate that the format of the Input String is:
not null
__=_% separated by commas
has a "DC=_%" RDN (relative distinguished name)
*/
if @InputString is not null
and (select count (value) from string_split(@InputString, ',') where value not like '__=_%') = 0
and patindex ('%dc=_%', @InputString) > 0
begin
/*
First, leave the first "DC=" and convert the rest of them to "."; so the string becomes:
'CN=XYZ123PW5OASDC,OU=SiteA,OU=Domain Controllers,DC=abc.def.ghi.jkl.com'
*/
select @WorkString = substring (@InputString, 1, patindex ('%,DC=%', @InputString))
+ replace (substring (@InputString, patindex ('%,DC=%', @InputString) + 1, len (@InputString)), ',DC=', '.')
/*
Put each part of the string into a temporary table (removing
the prefixed 2 character attribute string and the equal sign)
*/
insert into @SplitItems (ParsedValue) select right (value, len(value) - 3) from string_split(@WorkString, ',')
select @Rows = max(RowNumber) from @SplitItems
select @Counter = @Rows
-- Working backwards, concatenate the strings with '/' separators
while (@Counter > 1)
begin
-- Don't start it with a '/'
if @Counter = @Rows
set @WorkString = upper ((select ParsedValue from @SplitItems where RowNumber = @Counter))
else
set @WorkString = @WorkString + '/' + upper ((select ParsedValue from @SplitItems where RowNumber = @Counter))
set @Counter = @Counter - 1
end
end
else
select @WorkString = NULL
return @WorkString
end
Here is a sample:
System Center Configuration Manager Notes 