add users to security groups from excel spreadsheet

Hi


***environment***
windows 2003 AD

Maybe a quick and simple script but I am looking for a way to add a bunch of users (> 600) to their associated security groups, and have all this in excel and run as a script.

am happy to use vbscript, powershell...

example -
ColumnA   | ColumnB
user1        | List of security groups to add to
user2        | List of security groups to add to
user3        | List of security groups to add to
etc etc etc

I would then like to have a script that I can run and this will add the users to their required security groups...

The users will already be created, as are the groups - I just need to add users to a set of groups listed in excel...

I have had a look on EE and google but haven't (so far) found something that matches my requirements :-(

I appreciate any pointers, help on this..!

Thanks in advance
Bry
bryan oakley-wigginsSenior Cloud EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:

Hey,

If you're going for PowerShell, grab these first:

http://www.quest.com/activeroles-server/arms.aspx

And if you can drop it to a CSV file, because Excel is just unnecessary effort.

Could you tell us the format for "List of Security Groups"?

I'll use a semi-colon delimited list as an example. You could run...

ForEach ($Entry in (Import-CSV "TheFile.csv)) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}

Both columns would have to contain unique identifiers for each group / user. And the above is based on a CSV formatted like this:

ColumnA,ColumnB
username,group1;group2;group3

HTH

Chris
0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi Chris-Dent:

Thanks so much much for your response - This looks like exactly what I am looking for.
I am just install the quest tools so will be running the script on test shortly.

As for format of the list of groups, it will literally be:
ColumnA   | ColumnB
user1        | Systems Group, DBA_group, Finance etc
user2        | All_Users, WeB_group, invoices etc
user3        | Helpdesk_Group, MAIL_group, Finance etc

So I just place a comma after the user name I.e USER1,
The separate the groups with a semi-colon I.e GROUP1;Group2;Group3;etc ???

Is that correct?

Again, thanks so much for your time/code - very appreciated.

Cheers
Bry
0
Chris DentPowerShell DeveloperCommented:

Save it as .CSV in Excel. Once done, if you open up the file with Notepad you'll see the format I mean. PowerShell can natively read that where reading Excel is far more trouble than it's worth.

You'll need to give each column a name, otherwise PowerShell will use the first user / group combination as the column names.

And a comma delimited list for the groups is fine, we should find that Excel sticks quotes around them when you save it to CSV, making it:

user1,"Systems Group,DBS_Group,Finance"

Then we'd modify the code above to split on "," rather than ";":

ForEach ($Entry in (Import-CSV "TheFile.csv)) {
  ($Entry.ColumnB).Split(",") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}

If you want to see what PowerShell does when it reads the CSV just run:

Import-CSV "TheFile.csv"

That'll give you an Object which represents the contents of the file. It's that object we loop through to execute the commands.

Chris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Chris

that's brilliant..! - I'll give this a try and get back to you as soon as I can...
Thanks so much

Cheers
Bry
0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi

Getting this error when I run the script:

PS C:\> C:\Scripts\Test_Data\usr2grp.ps1
Encountered end of line while processing a string token.
At C:\Scripts\Test_Data\usr2grp.ps1:2 char:28
+   ($Entry.ColumnB).Split("," <<<< ) | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }

The import-csv comes back fine with the columns and the user and groups showing...

Do you happen to know what this may be?

Cheers
Bry
0
Chris DentPowerShell DeveloperCommented:

Hmm End of Line isn't expected there. Can you pop a sample of the CSV file you have at the moment? Can't quite see how it ended up with that particular error.

Chris
0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
sure, no problem

BTW - I had to save it as .xls as EE didn't except .csv upload <8-|)
attached is the csv (.xls) file - If I run import-csv I get the relevant user and groups in the respective columns A & B...

Cheers
Bry
IMP1.xls
0
Chris DentPowerShell DeveloperCommented:

Ahh okay, back to the semi-colon delimited list. Just needs this bit changing:

($Entry.ColumnB).Split("," <<<< )

To this:

($Entry.ColumnB).Split(";")

Then it can loop through each of the items in ColumnB for us.

Chris
0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi Chris

Sorry - I am getting this error now:

PS C:\> C:\Scripts\Test_Data\usr2grp.ps1
Missing closing ')' in expression.
At C:\Scripts\Test_Data\usr2grp.ps1:5 char:27
+   ($Entry.ColumnB).Split("; <<<< ") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }


The .ps1 is as follows:
ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv)) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}


Cheers
Bry
0
Chris DentPowerShell DeveloperCommented:

Missing a closing " after the file name here:

> ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv <<<)) {

Fixed:

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $_ -Member $Entry.ColumnA }
}

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi Chris

Apologies for delay in getting back to you, a production issue came up..!
I have now tested this again and brilliant, it works like a dream :-)

This is answered and I will accept your solution to the original question. I just wondered, how difficult it would be to reverse this so that in columnA I would have a group name and in columnB I would have users - so in affect, I would then run the script and have the group populated with the users..

I hope this makes sense and also, if I need to open a new question, I would be happy to do so.
as i say, I accept your fantastic solution.

thanks so much for your time.

Cheers
Bry
0
Chris DentPowerShell DeveloperCommented:

Aye, it does. Not much bother to change it:

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember $Entry.ColumnA -Member $_ }
}

It's pretty flexible about the values you can use, so it should be fine with that.

Chris
0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi Chris

Thanks so much for this, I am extrememly grateful.

 I just ran the script and I get the following error:

PS C:\> C:\Scripts\Test_Data\grp2usr.ps1
Add-QADGroupMember : Cannot resolve DN for the given identity: 'it_systems_staff_global;org_I
At C:\Scripts\Test_Data\grp2usr.ps1:2 char:54
+   ($Entry.ColumnB).Split(";") | %{ Add-QADGroupMember  <<<< $Entry.ColumnA -Member $_ }

I am sure it is to do with the ";" - How do I use this split in columnA also? (I would like to keep the ";" split in ColumnB as well, so that I can add multiple users :-)

Thanks again
Bry

0
Chris DentPowerShell DeveloperCommented:

Ahh sorry, didn't realise both were now semicolon separated.

Are you sure you're not making it a bit complicated by having both fields with 2 dimensions? :)

Anyway, I wonder if this might do it. It's a bit less effort (for AD) than the other version (or at least we've moved the effort elsewhere).

Chris
ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ForEach ($Group in ($Entry.ColumnA.Split(";")) {
    Add-QADGroupMember $Group -Member $($Entry.ColumnB.Replace(";", ","))
  }
}

Open in new window

0
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi Chris

Sorry - I was being a bit of a dufus..! It was exactly as you said and had provided the solution for - I just wanted the single group in 1 column and then the split of users in the other...
My apologies <*blush*>.
Tested and is working 100%.

I will use the other code snippit as well but for now, you have made my day..!

Thanks so much for your valuable time on helping me find a solution. Your a legend in my books..!

Cheers
Bry
0
Chris DentPowerShell DeveloperCommented:

Glad I could help :)

Chris
0
wlcsd-networkmanagerCommented:
This is what I've been looking for however, my needs are much more simple.  I have a csv file with column A having the single security group and column B with the username.  I've tried fiddling with the script above and I just don't have the skills.  Can you help me out, Chris-Dent?
0
Chris DentPowerShell DeveloperCommented:

Sure, can you give me a sample of what you have?

Chris
0
vanjay71Commented:
Hello, I have the exact same requirements as the person that posted 2009-09-17. I also only have one security group that I would like to add my users to.
I'm completely new to powershell. I installed the activeroles tool on my domain controller.
Do I just open up powershell and paste in the command from above?

ForEach ($Entry in (Import-CSV "c:\scripts\test_data\imp1.csv")) {
  ForEach ($Group in ($Entry.ColumnA.Split(";")) {
    Add-QADGroupMember $Group -Member $($Entry.ColumnB.Replace(";", ","))
  }
}
 
Of course I modified it so that it would take the name and path of my csv file.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.