Add variable to Select-Object command in powershell and output to excel csv

Question

I have a script that fetch user's manager info from a text file containing its respective groups and I want to put the results in an excel spreadsheet.

Also, I'd like to add a custom variable to the Select-Objects command so it shows up as another column in excel with each user's respective group

Here is what I have so far:

 $Properties = @( 'Title', 'Department', 'Manager' )

[string[]]$arrayFromFile = Get-Content -Path 'C:sslvpn-active.txt'
foreach($group in $arrayFromFile){
    $searchb = "CN="+"$group"+",OU=SSLVPN,OU=UserGroupsRAS,DC=xi,DC=xxxinc,DC=net"

    Get-ADGroupMember $searchb |
    Get-ADUser -Properties $Properties |
    Where-Object{ $_.Enabled } |
    Select-Object Name, Title, Department, 
        @{Name = "ManagerName"; Expression = { (Get-ADUser $_.Manager).Name }}, 
        @{Name = "ManagerMail"; Expression = { (Get-ADUser $_.Manager -Properties mail).Mail }} 
}

The results are currently

SSLVPN-ABC
----------------------
Name        : Joe Smith
Title       : IT Engineer 2
Department  : IT Support
ManagerName : Billy George
ManagerMail : [email protected]

Name        : Matt Damon
Title       : IT Engineer 3
Department  : IT Support
ManagerName : Billy George
ManagerMail : [email protected]

SSLVPN-XYZ
----------------------
Name        : Jen Loo
Title       : Product Designer 3
Department  : Product Design
ManagerName : Ben Smit
ManagerMail : [email protected]
...

I would like this result in excel format and also add a custom column called "Group" and have the group name (ex. SSLVPN-ABC) for each row. So the columns would be Group, Name, Title Department, ManagerName, ManagerMail and for the first group, the result cells will all be "SSLVPN-ABC".

Thanks

Answer 1

Change the select Statement like below.

Select-Object @{Name = "Group"; Expression = { $Group }}, 
    Name, Title, Department, 
    @{Name = "ManagerName"; Expression = { (Get-ADUser $_.Manager).Name }}, 
    @{Name = "ManagerMail"; Expression = { (Get-ADUser $_.Manager -Properties mail).Mail }} 

Here you're just adding another calculated property that will reference the $Group iteration variable. Note the properties are listed in the order you asked for them.

If by some chance the $Group variable doesn't hold the name you can use (Get-ADGroup $searchb).Name in the expression.

Export to Csv as normal, I believe you already resolved that part...