Active Directory (AD) User Object GUID

[vktr]

Hello,

Has anyone managed to get User Object GUID from Active Directory (AD)?

It is in binary format so it must be converted to text to use it.

There is an example on how to convert Object SID binary to text.

Also I saw a topic where MS Graph is used to get GUID, but it only applies to Azure AD, so it would not help in my case.

[Anonymous]

Thread revival from the grave, total PowerBI beginner.  How are you implementing;

= Binary.ToText(Guid, 0)

From the solutions above?

[camydee]

https://learn.microsoft.com/en-us/powerquery-m/binary-totext

[v-ljerr-msft]

Hi @vktr,

---

Has anyone managed to get User Object GUID from Active Directory (AD)?

It is in binary format so it must be converted to text to use it.

There is an example on how to convert Object SID binary to text. 

I just verified that I can successfully use the M query below which is provided in that thread to get User Object GUID from Active Directory (AD). 

let
    ConvertSidToStringSid = (sid) => let
        parts = BinaryFormat.Record([
            version = BinaryFormat.Byte,
            count = BinaryFormat.Byte,
            first = BinaryFormat.List(BinaryFormat.Byte, 6),
            rest = BinaryFormat.List(BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian))])(sid),
        result = "S-" & Number.ToText(parts[version]) & "-" & Number.ToText(BinaryFormat.UnsignedInteger64(#binary({0, 0} & parts[first]))) & List.Accumulate(parts[rest], "", (a, b) => a & "-" & Number.ToText(b))
    in
        result,
    Source = ActiveDirectory.Domains("redmond.corp.microsoft.com"),
    redmond.corp.microsoft.com = Source{[Domain="redmond.corp.microsoft.com"]}[#"Object Categories"],
    user1 = redmond.corp.microsoft.com{[Category="user"]}[Objects],
    FilteredRows = Table.SelectRows(user1, each [displayName] = "Curt Hagenlocher"),
    #"CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com" = FilteredRows{[distinguishedName="CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com"]}[securityPrincipal],
    objectSid = #"CN=Curt Hagenlocher,OU=UserAccounts,DC=redmond,DC=corp,DC=microsoft,DC=com"[objectSid],
    stringSid = ConvertSidToStringSid(objectSid),
    #"Converted to Table" = #table(1, {{stringSid}})
in
    #"Converted to Table"

Regards

[vktr]

Hi @v-ljerr-msft,

Yeah i know  I do use it for getting AD object's SID.

But as I've said, I needed to extract GUID value.

GUID is different from SID, so needed another solution to convert GUID's binary value to text, which turned up, is an eazy task.

[Anonymous]

@vktr did u find solution?...i have similar issue

[vktr · Accepted solution]

I needed this to link AD objects to Azure AD objects.

I have found that it is enough for me to convert AD GUID values with function:

= Binary.ToText(Guid, 0)

In Azure AD GUID's have the same expression as after confersion with function above.

[camydee]

Binary.ToText won't actually do what you want. This simply translates the bytes in sequence, which does not follow RFC 4122 GUID Format.

Try this:

Binary.ToTextGuid = (binaryGuid) =>
let
   Number.To2DigitHex = each Text.End("0"& Number.ToText(_, "X"),2),
   HexBytes = List.Transform(Binary.ToList(binaryGuid), Number.To2DigitHex),
   Result = Text.Format("#{3}#{2}#{1}#{0}-#{5}#{4}-#{7}#{6}-#{8}#{9}-#{10}#{11}#{12}#{13}#{14}#{15}", HexBytes)
in Result,