asked on

Powershell CSV import with group count

Lets say I have a CSV file with the first column all different AD groups. For example:

Column A
Z-Security Group1
Z-Security Group2
Z-Security Group3
etc...

How can I import the CSV file and then run a loop to count total number of members for each group and export the said data to another spreadsheet with output like:
Column A Column B
Z-Security Group1 22
Z-Security Group2 50
Z-Security Group3 0
etc.

Thanks for all your ideas and help.

oBdA

Import-Csv -Path 'C:\Temp\import.csv' |
	Select-Object 'Column A', @{n='Column B'; e={Get-ADGroupMember -Identity $_.'Column A' | Measure-Object | Select-Object -ExpandProperty Count}} |
	Export-Csv -NoTypeInformation -Path 'C:\Temp\export.csv'

Open in new window

Qlemo

Import-CSV C:\Temp\EE\Input.csv -Header grp | select -Skip 1
  group -NoElement grp |
  Select @{n='grp'; e={$_.Name}}, Count |
  Export-CSV -NoType C:\Temp\EE\Output.csv

Open in new window

As we do not know the exact name of the CSV input, I have provided a fixed name and skip the first row (containing the real column names). The remainder should be obvious.

Qlemo

I assumed the CSV input contains the resolved AD groups plus members - rereading the question, I'm not certain that is the case, and oBdA might be correct.

Robert Van Dyke

ASKER

No luck on either of the solutions although I appreciate the effort.

I should have made it more clear the columns aren't actually named but just for more of a reference...

Here's a picture of the csv I'd like to import.

And an example of what I'd like my export file to look like. I hope this makes it a bit more clear, thanks so much.

SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Van Dyke

ASKER

@oBdA

Awesome man, thanks so much! Works exactly as I needed.

One last question... This could be a separate script or a combination but output to a second file...

What if I wanted to list each group and then in ColB list all the members of each group?

Once the first group is done, move onto the 2nd group and continue?

SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Van Dyke

ASKER

@oBdA

Last question I swear, one last request to the original script.

Import-Csv -Path 'G:\IS\everyone\Security\AD LicenseCounts\InputADGroupAllowedCntExample.csv' -Header 'SecurityGroupName' |
	Select-Object 'SecurityGroupName', @{n='UserCount'; e={Get-ADGroupMember -Identity $_.'SecurityGroupName' | Measure-Object | Select-Object -ExpandProperty Count}} |
	Export-Csv -NoTypeInformation -Path 'G:\IS\everyone\Security\AD LicenseCounts\InputADGroupAllowedCntExampleResults.csv'

Open in new window

If my original CSV had 2 colums, group names and max license count, could I export exactly what you had originally done but also include in a 3rd column the originally included max license count?

Example Input file

Example Output file

Seriously thank you so much, you've been extremely helpful, If I could gift you gold I would :)

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Van Dyke

ASKER

oBdA was amazing, would ask Questions again!