CODEWITHSUNDEEP
powershellactive-directory
Jim
Jim

Reputation: 11

Formatting output from PowerShell to a csv file

Trying to output two variable results from Active Directory to PowerShell and format on one line of a .csv file with each returned variable in separate cells. The input file contains the names of four AD groups. The task is to count the users in each group and return the total number of users.

The goal is to write to the .csv file formatting the output with the ad group name in one cell and the user count in the next cell to the right.

This is a simple script built by starting with reading the file and returning the results to the screen. Attempted to write to the file using Export-Csv with no success. The Add-Content has been the most successful technique.

The following code works part of the way, but the ad group name and the user count is written to the same cell in the .csv file. Earlier attempts wrote the AD group name on a line and the total # of users on the next line. 

foreach($object in $groupfile){
    $groupName = $object.adgroupname
    $groupName = $groupName.Trim()
    $users = Get-ADGroupMember -Identity $groupName
    $usercount = $users.Count
    $groupinfo = ($groupName + " " + $usercount)

    # both of the lines below return the information to the screen on on line
    Write-Host $groupname, $usercount
    Write-Host $groupinfo

    # this line returns the information as shown below in the First result
    Add-Content -Path $filepath $groupname, $usercount

    # this line writes to the file as shown below in the Second result
    Add-Content -Path $filepath $groupinfo
}

First result (acceptable for a small number of groups, but a more robust solution is necessary for a larger number of groups.): ad group name one 357 ad group name two 223 ad group name three 155 ad group name four 71

Second result (both values for the returned variable are in one cell): ad group name one 357 ad group name two 223 ad group name three 155 ad group name four 71

The goal is to write to the .csv file formatting the output with the ad group name in one cell and the user count in the next cell to the right.

Upvotes: 1

Views: 1188

Answers (1)

Theo
Theo

Reputation: 61028

I'm assuming your variable $groupfile is the result of a Import-Csv command.

If I understand the question properly, you could do this:

# loop through the $groupfile rows and collect the results as objects
$result = $groupfile | ForEach-Object {
    $groupName = $_.adgroupname.Trim()
    [PSCustomObject]@{
        'GroupName' = $groupName
        'UserCount' = @(Get-ADGroupMember -Identity $groupName).Count
    }
}

# display on screen
$result | Format-Table -AutoSize

# export to csv
$result | Export-Csv -Path $filepath -NoTypeInformation -Force

Should output a csv, something like 

"GroupName","UserCount"
"ad group name one","357"
"ad group name two","223"
"ad group name three","155"
"ad group name four","71"

Note: the Get-ADGroupMember can return objects of type user, group and computer, so either name the second column ObjectCount of add a Where-Object {$_.objectClass -eq 'user'} clause to the Get-ADGroupMember function to filter only user objects

Hope that helps

Upvotes: 2

Related Questions