Solved

Move AD Users into groups by csv input file

Posted on 2010-11-28
37
904 Views
Last Modified: 2012-05-10
Hello,

I have to write a bit complicated powershell script which definitely exeeds my knowledge with powershell. Here´s the problem:

I have 6 different groups for a special purpose to define a user right. The users will be member of only one of this 6 groups but can be members of other groups even also.
A CSV-File is created once a day that describes which user should be moved from one of the 6 grous to antoher of the 6. Other groupmemberships should not be affected by this move.

The CSV will look like this:
User;Group
user1;SSO_group1
user2;SSO_group2
user3;SSO_group1

(All of the 6 Groups will have SSO as prefix. That should make the identification easier in the script).

I have the task to create a script that does the moving of users and creates a logfile. Quest Tools are available for this task.

Any help would be appreciated!

Thanks & Regards
Steffen
0
Comment
Question by:SSR-IS
  • 23
  • 14
37 Comments
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Try this. It will check all the groups the user is a member of and get the sso group and remove. Then add the sso group in the csv file. This assumes that the user will only be a member of a single sso group. if it is possible the user will be a member of several sso groups then we can change the script to remove from all with another foreach loop.

$users = import-csv c:\users.csv
foreach ($User in $Users){
$ssogrp = get-qadmemberof $user | where {$_.name -match "SSO_"}
remove-qadgroupmember $ssogrp $user.user
add-qadgroupmember $user.group $user.user
}
0
 

Author Comment

by:SSR-IS
Comment Utility
Hi KenMcF,

thanks for your quick relpy!

I get an error running this script:
Line 4 Char 26 Argument should not be NULL or empty
(Sorry, I need to translate this from german...)

The error comes up for every remove and every add command.
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
so the users may not be in any of the sso groups then.

$users = import-csv c:\users.csv
foreach ($User in $Users){
$ssogrp = get-qadmemberof $user | where {$_.name -match "SSO_"}
if ($ssogrp -ne $null){remove-qadgroupmember $ssogrp $user.user}
add-qadgroupmember $user.group $user.user
}
0
 

Author Comment

by:SSR-IS
Comment Utility
Sorry, I´m still getting errors like:

Get-QADMemberOf : Es wurde kein Parameter gefunden, der dem Parameternamen "@{User;Group=H_User;SSO}" entspricht. Bei D:\sso\move5.ps1:3 Zeichen:26

Which means: No parameter found that corresponds to parameter name "@{User;Group=H_User;SSO}"

Do we need to give any AD information like domain or something to the script? As I read now, the QAD-Commands user an -indentity parameter...
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Had a mistake in the get-qadmemebrof line, try this one.

$users = import-csv c:\users.csv
foreach ($User in $Users){
$ssogrp = get-qadmemberof $user.user | where {$_.name -match "SSO_"}
if ($ssogrp -ne $null){remove-qadgroupmember $ssogrp $user.user}
add-qadgroupmember $user.group $user.user
}
0
 

Author Comment

by:SSR-IS
Comment Utility
It´s not working with this.
What I found out is, that if I use the qet-qadmemberof command from your script as a single command, I get no results (or better: an empty result).
When I user this command: >Get-QADMemberOf -identity 'test.local/test/SSO-Testuser/H_User' | where {$_.name -match "SSO"}
then I get the groups containing "SSO" returned.

Can you give me the syntax for building the OU-Information into your script? I think we should give this a try.
0
 

Author Comment

by:SSR-IS
Comment Utility
Hmmm...
It might not be an issue with the OU-Information. If I run the get-qadmemberof command with an username specified, I´m getting the correct results without providing the OU.

Maybe it´s an issue with the import format?
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
ok, I think the user is a member of multiple SSO groups. So we will need to add another foreach loop.

$users = import-csv c:\users.csv
foreach ($User in $Users){
$ssogrp = get-qadmemberof $user.user | where {$_.name -match "SSO_"}
if ($ssogrp -ne $null){
     foreach ($sso in $ssogrp){
          remove-qadgroupmember $sso.dn $user.user
     }  
}
add-qadgroupmember $user.group $user.user
}
0
 

Author Comment

by:SSR-IS
Comment Utility
I think there´s an empty variable:
Get-QADMemberOf : The argument is no allowed to be NULL or empty.
At D:\sso\move8.ps1:3 Character:26

When I remove the group information from the CSV, rename it into TXT and change import-csv with get-content, then I get the groups back:
$users = get-content d:\sso\import.txt
foreach ($user in $users)
{
get-qadmemberof $user
}

Otherwise I get the error message with an emty argument...
0
 

Author Comment

by:SSR-IS
Comment Utility
Okay, okay!!!
I replaced the semikolons in my CSV with commas and now it works!!
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
missed the semi-colons in you original post.  good catch.
0
 

Author Comment

by:SSR-IS
Comment Utility
Am I right when I think that with this script a user can never be assigned to more then one SSO group? If so, then it´s the was it should be!

I don´t want to be cheeky, but may I ask you for implementing some extra features in this script?

I could need an export-csv for all group membership changes:
 If any line in the csv could not be applied this should be documentet in this CSV.
 If a user account was not found in AD there should be a message in the output CSV.
 It would be very fine if the information in export-csv could be something like:
 USER      |        OLD GROUP    |     NEW GROUP

0
 
LVL 27

Accepted Solution

by:
KenMcF earned 500 total points
Comment Utility
Yes, this will remove all SSO groups and only add the one in the csv file.


Try this for you output. It will be hard if the users was a member of multiple SSO groups when the script runs. So the output will record if the user was not found, If they were added, and what group was added.
$out = @()

$users = import-csv c:\users.csv

foreach ($User in $Users){

if((get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(samaccountname=$($user.user)))") -eq $null){

$out += "$($user.User), Could not find"}

$ssogrp = get-qadmemberof $user.user | where {$_.name -match "SSO_"}

if ($ssogrp -ne $null){

     foreach ($sso in $ssogrp){

	  $out += "removed,$($user.user),$($sso.name)"

          remove-qadgroupmember $sso.dn $user.user

     }   

}

add-qadgroupmember $user.group $user.user

$out += "Added,$($user.user),$($user.group)"

} 

$out | out-file c:\output.csv

Open in new window

0
 

Author Comment

by:SSR-IS
Comment Utility
Hello!
Output works fine!
There´s just one thing I noticed when I look at the output: Even if the user is already member of the correct group, the group gets removed and then added again. This would mean a lot of unnecessary workload if you run the script against a bigger number of users.

And is it possible to add a headline to the output file? That would make it easier to filter it after Excel import.

removed,H_User,SSO_
Added,H_User,SSO_
removed,H_User-L,SSO_L
Added,H_User-L,SSO_L
removed,H_User-XL,SSO_XL
Added,H_User-XL,SSO_XL
removed,H_User-Priv,SSO_PRIV
Added,H_User-Priv,SSO-PRIV
removed,H_User-L-PRIV,SSO_L-Priv
Added,H_User-L-PRIV,SSO_L-PRIV
removed,H_User-XL-PRIV,SSO_XL-Priv
Added,H_User-XL-PRIV,SSO_XL-PRIV

Thanks a lot for your fine work!
0
 

Author Comment

by:SSR-IS
Comment Utility
I have another problem with qadgetmemberof -match option in the script. -match 'SSO' gives back any group which contains SSO in it´s name. And this are more than the 6 groups in which I need to move the users around.
How would the script-syntax be if I would like to name all six groups?
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Do the other groups match "SSO_" ?

You could put them into an array

$groups = "SSO_Grp1", "SSO_Grp2", "SSO_Grp3"

then loop through the array.



0
 

Author Comment

by:SSR-IS
Comment Utility
Sorry I did not clearly define the SSO-Group names in my initial question...

I was trying around a little bit and found following solution:

I define the variable $targetgrps with the name of groups I need to check. Then the line of code is:
$ssogrp = get-qadmemberof $user.user | where {$targetgrps -like $_.name}.

Thanks for your reply!!!

I leave this question open, I´m still wokring on the script ;-)
0
 

Author Comment

by:SSR-IS
Comment Utility
So, I got another question:

Is it possible to process a CSV-File thats semikolon-seperated instead of comma seperated?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Yeah you can use this command

import-csv c:\users.csv -Delimiter ;
0
 

Author Comment

by:SSR-IS
Comment Utility
Oh great. Somtimes a solutions is so easy... ;-)
0
 

Author Comment

by:SSR-IS
Comment Utility
That works fine (if you set ; in ';'). Thank you!

If I run this script with a bigger number of users, it would be very nice to see the progress on screen - or the currently processed user or something. Just because there´s nothing to see for a longer time an all is written to logfile.
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
If you want to see the progress when manually running this you could just do this


remove-qadgroupmember $sso.dn $user.user
write-host $sso.dn
0
 

Author Comment

by:SSR-IS
Comment Utility
Yeah, thanks- that will do the job!
0
 

Author Comment

by:SSR-IS
Comment Utility
At least I have added some code to email the logfile after the script was run. Unfortunatly I get an error message, although the email and the logfile are sent correctly.

Message:
Out-File : The process cannot access the file 'C:\sso\ssolog_2010-11-30.csv' becaus
r process.
At C:\sso\test-ssochange2.ps1:18 char:16
+ $out | out-file <<<<  ("c:\sso\ssolog_" +$Date + ".csv")
    + CategoryInfo          : OpenError: (:) [Out-File], IOException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

And here´s the complete code:
(Don´t wonder, I removed the add/ remove commands for testing...)
# Add-PSSnapin Quest.ActiveRoles.ADManagement

$out = @()

$targetgrps = "SSO", "SSO_L", "SSO_XL", "SSO-priv", "SSO_L-priv", "SSO_XL-priv"

$users = import-csv c:\sso\import.csv -Delimiter ';'

foreach ($User in $Users){

if((get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(samaccountname=$($user.user)))") -eq $null){

$out += "$($user.User),No action,Could not find"}

$ssogrp = get-qadmemberof $user.user | where {$targetgrps -like $_.name}

if ($ssogrp -ne $null){

     foreach ($sso in $ssogrp){

     $out += "$($user.user),will be removed from,$($sso.name)"

     write-host $user.user

     }   

}

$out += "$($user.user),will be added to,$($user.group)"

}

$Date = (Get-Date).tostring("yyyy-MM-dd")

$out | out-file ("c:\sso\ssolog_" +$Date + ".csv")





# Send email after run was processed



$file = ("c:\sso\ssolog_" +$Date +".csv")



$smtpServer = "server.domain.com"



$msg = new-object Net.Mail.MailMessage

$att = new-object Net.Mail.Attachment($file)

$smtp = new-object Net.Mail.SmtpClient($smtpServer)



$msg.From = "SSO-Change@domain.com"

$msg.To.Add("mail@domain.com")

$msg.Subject = "SSO change run finished"

$msg.Body = "SSO change script run from " +$Date + " was executed!"

$msg.Attachments.Add($att)



$smtp.Send($msg)



$att.Dispose()

Open in new window

0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Not sure off hand why you are getting that error, it does not look like that file would be in use. I can do some testing later.

For sendign the email you may to look at send-mailmessage cmdlet
0
 

Author Comment

by:SSR-IS
Comment Utility
I would be happy to hear from you later!
Sending the email (with attachment) works fine, it´s just this error at the end of the script...
0
 

Author Comment

by:SSR-IS
Comment Utility
May I ask for another thing?
I had a little misunderstanding with my project... I will not be provided with a CSV that contains the target group of the user, but the username and the actual group. The user then should be moved by the following pattern:

Old Group     New Group
SSO              SSO-priv
SSO_L          SSO_L-priv
SSO_XL        SSO_XL-priv

There should be no moving between SSO/ SSO_L/ SSO_XL. The problem for the code is, the CSV file contains the old group, not the target.

Would you mind adapting the code? I´m really sorry about this...
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Could you provide a sample of the csv file so I can fully understand.
0
 

Author Comment

by:SSR-IS
Comment Utility
Okay, an example would be:
approvalstep;fillusername;useradgroup;usermail;userfullname
cancel;testuser001;SSO;testuser001@test.com;user001, test
step1;testuser002;SSO;testuser002@test.com;user002, test
step1;testuser003;SSO_L;testuser003@test.com;user003, test
step1;testuser004;SSO_XL;testuser004@test.com;user004, test
step1:testuser005;SSO-priv;testuser005@test.com;user005, test

Not all fields are requiered for me (e.g. usermail, userfullname).
Now step by step:
testuser001 >> do nothing, approval is canceled
testuser002 >> move to SSO-priv
testuser003 >> move to SSO_L-priv
testuser004 >> move to SSO_XL-priv
testuser005 >> do nothing, user is already member of a "-priv" group

So it´s all about movenment from one SSO-Group to the according -priv group. If someone is member of SSO he will only become a member of SSO-priv (as long a he´s listet in the CSV and approvalstep is not "canceled"). A member of SSO_L can only become a member of SSO_L-priv, and so on...

Thanks again!
0
 

Author Comment

by:SSR-IS
Comment Utility
Hey KenMcF,

should I close this here and open a new question for the changed script requirements?
Would be no prob!
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
That woudl be up to you if you want to open a new question.
0
 

Author Comment

by:SSR-IS
Comment Utility
I just thought about your points.
500 for all your work might be a little to less...

Would you mind rewriting the code a last time?
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
I will take a look at this later tonight and post back.
0
 

Author Comment

by:SSR-IS
Comment Utility
That´s absolutely great!
I´m a bit lost in this coding project...
0
 
LVL 27

Expert Comment

by:KenMcF
Comment Utility
Try this one, i think that should work if I understand your requirements.

 
Add-PSSnapin Quest.ActiveRoles.ADManagement  

$out = @()  

$users = import-csv c:\sso\import.csv -Delimiter ';'  

foreach ($User in $Users){  

if((get-qaduser -ldapfilter "(&(objectcategory=person)(objectclass=user)(samaccountname=$($user.fillusername)))") -eq $null){  

	$out += "$($user.fillusername),No action,Could not find"

}

Else{  

	$out += "$($user.fillusername),removed from $($user.useradgroup), Added to $($user.useradgroup)-priv"  

	write-host $user.fillusername

	remove-qadgroupmember $user.useradgroup $user.fillusername

	add-qadgroupmember "$($user.useradgroup)-priv" $user.fillusername

}

  



}  

$Date = (Get-Date).tostring("yyyy-MM-dd")  

$out | out-file ("c:\sso\ssolog_" +$Date + ".csv")

Open in new window

0
 

Author Comment

by:SSR-IS
Comment Utility
Thanks again!
I´ll close this for now - if I have any other questions I will open a new one.
0
 

Author Closing Comment

by:SSR-IS
Comment Utility
Thanks KenMcF for his endless patience!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now