Add variable to top of format-table

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #5599

    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]

You must be logged in to reply to this topic.