• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • Last Modified:

Move AD Users into groups by csv input file

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
SSR-IS
Asked:
SSR-IS
  • 23
  • 14
1 Solution
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
Okay, okay!!!
I replaced the semikolons in my CSV with commas and now it works!!
0
 
KenMcFCommented:
missed the semi-colons in you original post.  good catch.
0
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
So, I got another question:

Is it possible to process a CSV-File thats semikolon-seperated instead of comma seperated?
0
 
KenMcFCommented:
Yeah you can use this command

import-csv c:\users.csv -Delimiter ;
0
 
SSR-ISAuthor Commented:
Oh great. Somtimes a solutions is so easy... ;-)
0
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
Yeah, thanks- that will do the job!
0
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
Could you provide a sample of the csv file so I can fully understand.
0
 
SSR-ISAuthor Commented:
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
 
SSR-ISAuthor Commented:
Hey KenMcF,

should I close this here and open a new question for the changed script requirements?
Would be no prob!
0
 
KenMcFCommented:
That woudl be up to you if you want to open a new question.
0
 
SSR-ISAuthor Commented:
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
 
KenMcFCommented:
I will take a look at this later tonight and post back.
0
 
SSR-ISAuthor Commented:
That´s absolutely great!
I´m a bit lost in this coding project...
0
 
KenMcFCommented:
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
 
SSR-ISAuthor Commented:
Thanks again!
I´ll close this for now - if I have any other questions I will open a new one.
0
 
SSR-ISAuthor Commented:
Thanks KenMcF for his endless patience!
0

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

  • 23
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now