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.

[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.

[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.