As I was walking our business users through the role management features of the K2 Workspace, they lamented that they K2 didn’t give them visibility inside of the Active Directory groups which are assigned to various roles. My quick solution was to provide them with a PowerShell script which would give them the insight they were looking for.

I can’t do anything about the Workspace interface, but I can give them another tool to get them the information they need. Using AD groups is something completely new for our business users – so I wanted to give them a friendly interface for the information they needed.

I’m a little old school. I’ve touted the wonders of the net group command before. Showing them how to issue the command from cmd.exe nearly made their heads explode. I needed something… softer. More clicky-clicky and less typey-typey.

So my solution was essentially a Powershell-based wrapper for the net group command. With a few embellishments. About the only unusual thing they’d have to do is right-click on the .ps1 and choose “Run with Powershell” to execute it. The script produces a comma-separated values (.CSV) file, which Microsoft Excel opens by default. What could be simpler?

The script below is very likely not a textbook example of how to write PowerShell scripts. There are definitely some elements that can use some tightening up. The term “mypattern” below is meant to be a generic expression for whatever naming schema your organization uses for creating project-oriented AD groups. Flavor to taste, your mileage may vary, et cetera.


# If you're reading this, you may have double-clicked on the file to run it.
# The file won't run this way. Close this file, then right-click on it and
# select "Run with Powershell" from the context menu.

# GetNames -- gets the full names of users from a list of usernames passed to it.
Function GetNames{

param(
[String] $memberslist
)

$script:namelist = $null

#do some clean-up on the parameter list
$memberslist = $memberslist -replace " ", ""
$memberslist = $memberslist -replace ",", "; "

#split the parameter list into an array
$separator = "; "
$option = [System.StringSplitOptions]::RemoveEmptyEntries
$members = $memberslist.Split($separator, $option)

foreach ($member in $members) {
If($member){
$name = invoke-command {net user $member /domain |
where {$_ -AND $_ -match "Full Name"}
}

#format the name
$name = $name -replace "Full Name", ""
$name = $name.Trim()

#add it to the name list
$script:namelist += $name + ", "
}
}
$script:namelist = $script:namelist -replace ", $", ""

}

$filename = "group_members.csv"
$outarray = @()
$date = (Get-Date -UFormat "%m/%d/%Y %I:%M %p").ToString()
$grouplist = $null

# get a list of AD groups which match the pattern "mypattern".
$my_groups = invoke-command {net group /domain | where {$_ -AND $_ -match "mypattern"} }
$separator = "; "

# do some cleanup on each string in the list
# and create a $grouplist var to hold all the cleaned strings
foreach($my_group in $my_groups) {
If($my_group) {
$my_group = $my_group -replace "*", ""
$my_group = $my_group.Trim()
$grouplist += $my_group + $separator
}
}

# operate on $grouplist
# split into an array called $groups
$grouplist = $grouplist -replace ", $", ""
$option = [System.StringSplitOptions]::RemoveEmptyEntries
$groups = $grouplist.Split($separator, $option)

# get the membership of each group
foreach($group in $groups) {
If($group) {
$members = invoke-command {net group $group /domain |
where {$_ -AND $_ -notmatch "command completed successfully" -AND $_ -notmatch "Comment"} |
select -skip 4}

#format the members string
$members = $members -replace " ", ","
$members = $members.Trim()
$members = $members -replace ",$", ""

#get the full name of each user from the function
GetNames($members)

#construct an object
$myobj = "" | Select "GROUP", "MEMBERS", "DATE"
$myobj.group = $group
$myobj.members = $script:namelist
$myobj.date = $date

#send the object to the output
$outarray += $myobj

#tidy up
$myobj = $null
}
}

#push it out to CSV. The NoTypeInformation argument prevents a #TYPE header from being planted in the CSV
$outarray | export-csv $filename -NoTypeInformation

A note or two:

I encouraged the business people to consider copying the .ps1 into a separate folder. That way, when it runs, the CSV will be planted in the same folder.

I also encouraged them not to keep the resultant CSV around — the whole point of the script was to generate the listing on demand. The date column is included in the output file to sort of remind them of that. (By the way, Excel forces its own formatting on the file data. Even though the date is sent in 12 hour format, it doesn’t appear that way in the column.)

If the output file is open while the script is executed, the script will give an exception.

Finally, the membership in the AD groups does not necessarily reflect the membership in the K2 roles. Yes, we named the AD groups very similarly to the process roles they’re used in, but the business is aware they can add users to roles directly on an as-needed basis. The distinction is important!

Anyway, that was my quick solution to their business need. I hope it helps you if you find yourself in a similar situation.