Excel list of users in AD Groups

Question

I have a list of user names in an excel file, I want to write a powershell script to tell if each user is in a group that contains a string in an AD group. The AD groups are all different names but contain "-String" at the end of the AD group name. I want the input to be an excel file of names to check and the output be a list of names NOT in the list.

Easy to do with PowerShell?

Answer 1

It's relatively easy.

First, using an Excel file directly isn't something you can do with PowerShell. You can export that Excel spreadsheet to a CSV file to remove that impediment. Or you can install the ImportExcel Powershell module and use it to work directly from the Excel file. (https://www.powershellgallery.com/packages/ImportExcel/7.8.9).

I think the approach that would make the most sense in your description of the problem would look something like this:

• Each AD object that is a member of any AD group has a property named "memberof". That property contains the distinguishedName of all the groups of which the user (in your case) is a direct member.
• Get the distinguishedName property from each AD group whose name ends with "-String" and place them in an array (or a hashtable where the key is the groups DN).
• For each user in your list, get that users "memberOf" property and check each DN in that multivalued property against the set of group DNs.
• If the users memberOf contains none of the group DNs, then do something with that user to identify it as not being a member of any of your *-String groups.

What I'm not sure of is the meaning of the last sentence in you question:

I want the input to be an excel file of names to check and the output be a list of names NOT in the list.

I'm assuming you meant ". . . NOT in any of the *-String groups."