Author Posts

January 1, 2012 at 12:00 am

by badpig521 at 2013-02-26 11:22:02

Yea I know, the subject is terrible. I wasn't sure how to word it but here is what I have:
[code2=powershell]cls
# Configuration data
[string]$server = "SERVER_NAME";
$outfile = "C:\SCRIPTS\Powershell\SQL\Security\AD_GROUP_MEMBERS_$server"+"_"+ $(get-date -Format 'dd_MM_yyyy')+".txt"

Add-Type -AssemblyName System.DirectoryServices.AccountManagement;
Clear-Host;

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=master;Integrated Security=True;";
$con.open();

# Select-Statement for AD group logins
$sql = "SELECT [loginname]
FROM sys.syslogins
WHERE [isntgroup] = 1
AND [hasaccess] = 1
AND [loginname] <> 'BUILTIN\Administrators'
ORDER BY [loginname]";

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$rd = $cmd.ExecuteReader();

$ads = [System.DirectoryServices.AccountManagement.ContextType]::Domain;

while ($rd.Read())
{
[string] $groupName = $rd.GetString(0);
$group = [System.DirectoryServices.AccountManagement.GroupPrincipal]::FindByIdentity($ads, $groupName);
if ($group)
{
Write-Host "Writing Members of AD Group: $groupName to File" -ForegroundColor DarkBlue;
$group.GetMembers($true) | Sort-Object UserPrincipalName | Format-Table UserPrincipalName, DisplayName, EmailAddress -AutoSize >> $outfile
}
}

# Close & Dispose all .NET objects.
$rd.Close();
$rd.Dispose();
$cmd.Dispose();
$con.Close();
$con.Dispose();

notepad $outfile[/code2]

results look like this:
[code2=plain]UserPrincipalName DisplayName EmailAddress
—————– ———– ————[/code2]

I need to add the $groupname to the top of each result, Like this:
[code2=plain]ADGroupName
UserPrincipalName DisplayName EmailAddress
—————– ———– ————[/code2]

Any ideas?

by poshoholic at 2013-02-26 11:38:18

Pipe your results to sort by ADGroupName, then use the -GroupBy property of Format-Table.

For example, look at this output for services:
Get-Service | Sort-Object -Property Status | Format-Table -Property Name,DisplayName -GroupBy Status

by poshoholic at 2013-02-26 11:39:59

Oh, and if you don't have ADGroupName as a property for the objects you return, then add it first using Add-Member by inserting this before the sort:
... | Add-Member -Name ADGroupName -MemberType NoteProperty -Value $groupName -PassThru | ...

by ArtB0514 at 2013-02-26 11:41:23

Have you tried this:
if ($group) {
Write-Host "Writing Members of AD Group: $groupName to File" -ForegroundColor DarkBlue;
$groupName >> $outfile
$group.GetMembers($true) | Sort-Object UserPrincipalName | Format-Table UserPrincipalName, DisplayName, EmailAddress -AutoSize >> $outfile
}

by badpig521 at 2013-02-26 12:23:46

Thank you all for your help! I really appreciate it. Looks like this worked for me:

[code2=powershell]Write-Host "Writing Members of AD Group: $groupName to File" -ForegroundColor DarkBlue;
"`n$groupName" >> $outfile
$group.GetMembers($true) |Sort-Object UserPrincipalName |Format-Table -Property UserPrincipalName, DisplayName, EmailAddress -AutoSize >> $outfile[/code2]