Getting GID or SID fom Active Directory

[NicoLemarchand]

Hi, 

I'm facing a problem I'm not able to solve alone.

I analyse usage of a web app thanks to a powerBi dashboard.

Currently, as all the logs of my app identifies users with their SID, I shall ask my IT department to export a list linking SID and usernames (I think they did that with PowerShell) and then I can import my logs, this table, and I connect Active Directory to get all relevant data I need about my users.

I guess I could be possible to get directly the SID from Active Directory in PowerBi but I dont find it in all the tables. I found a user GID (I'my quite sure it's the userful info for me) but it's a binary.

In fact, what I expect is to get a table with :

UserName | SID or GID | then all other info i alerady get from the AD (users mail; department, etc.)

Can someone help on that ?

Feel free to ask for more details if needed !

Nicolas.

[TrentoTrent]

Built a custom Power M function that transforms the objectSid binary into it's string representation - Based off of: c# - How to convert SID to String in .net - Stack Overflow

This will work for SIDs as defined in to the best of my knowledge: Security identifiers | Microsoft Learn

(objectSid as binary) =>
let
    buffered_binary = Binary.Buffer(objectSid),
    binary_length = Binary.Length(buffered_binary),
    version = BinaryFormat.Byte(Binary.Range(buffered_binary,0,1)),
    number_of_sub_authorities = BinaryFormat.Byte(Binary.Range(buffered_binary,1,1)),
    identifier_authority = BinaryFormat.UnsignedInteger64(Binary.Combine({Binary.FromList({0,0}), Binary.Range(buffered_binary,2,6)})),
    UnsignedInteger32_le = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
    result = List.Generate(()=> [x=0, y=if number_of_sub_authorities > 0 then Text.From(UnsignedInteger32_le(Binary.Range(buffered_binary,8,4))) else ""], each [x] < number_of_sub_authorities, each [x = [x]+1, y = Text.From(UnsignedInteger32_le(Binary.Range(buffered_binary,8 + 4*([x]+1),4)))], each [y]),
    sub_auth_string = if number_of_sub_authorities > 0 then "-" & Text.Combine(result, "-") else ""
in
    "S-" & Text.From(version) & "-" & Text.From(identifier_authority) & sub_auth_string

[NicoLemarchand]

Hi @lbendlin 

Thank you for the quick reply.

I searched a little bit further in the tables and I'm quite sure the info I need is contained in the "ObjectSid" contained in the securityPrincipal table for each of the users in AD.

I wish I could both expand securityPrincipal to get the objectSID (easy) and make it "human readable" . Thats the point where I'm struck...

You're right on the point SID are not fully user related. Globally, the first part of the SID is linked to the domain, and it's the same for all users of that domain. But the last part of the SID is related to user. For me, having the whole SID or only the user related part "GID ? or RID?" is ok, as ally my users are on the same domain and I can have one with the other.

[BrownChad]

Were you able to find a resolution for this? I'm trying to do something similar but with the ObjectGUID value. I'm struggling to figure out how to get the value from being seen as Binary to the RfC 4122 GUID Format. Any help would be great!

[lbendlin]

In our AD implementation the GID is called "primaryGroupID"  and it sits inside the "user"  attribute.

let
    Source = ActiveDirectory.Domains("domain.net"),
    domain = Source{[Domain="domain.net"]}[#"Object Categories"],
    user1 = domain{[Category="user"]}[Objects],
    #"Filtered Rows" = Table.SelectRows(user1, each Text.Contains([distinguishedName], "username")),
    #"Expanded user" = Table.ExpandRecordColumn(#"Filtered Rows", "user", {"primaryGroupID"}, {"primaryGroupID"})
in
    #"Expanded user"

As I understand it SIDs are machine related, not user related.