Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
Thread revival from the grave, total PowerBI beginner. How are you implementing;
= Binary.ToText(Guid, 0)
From the solutions above?
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
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.
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.
Not sure where or how to add this function. Anymore details available?
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,
User | Count |
---|---|
91 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |