Link to home
Start Free TrialLog in
Avatar of Mandy_
Mandy_

asked on

Powershell - could anybody check my script. Nothing happens with testuser

Hi,

can anybody helps with my complete script below. Neither EMS nor powergui is doing anything.

I think the error should be in the incorrect CSV.
It's only reading the last entry of my CSV.

I tried to use the script below to converting any form of csv to a format that will be read with import-csv

(import-csv -delimiter ';' c:\export_9.csv | sort product, version -descending | convertto-csv -notype -delimiter ',') -replace '"' | out-file c:\export_19.csv

Open in new window


The output is this i think it should be the right one
UserId,OrderId,Action,Product
Z000001,223149,New,AdAccount
Z000002,157827,New,AdAccount
Z333333,157827,New,AdAccount
Z555555,157827,New,AdAccount

Open in new window


How can i convert and check if the CSV converted to the correct form?

here my complete script:
Import-Module activedirectory


ForEach ($User in  Import-Csv "c:\export_9.csv"){ 

  If($user.Version -like "C2010") {
  If($user.Action -like "new") {
  
  $DBuser = (Get-User -identity $user.UserID).department
            If ($DBuser -like "*EMC*") {
                $db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
                Enable-Mailbox -Identity $user.UserId -Database $db
                }
            
			else {
                Enable-Mailbox -Identity $user.UserId
                }       
            
  } # end if create
  If($user.Action -like "Delete") {         
   Disable-Mailbox -Identity $user.UserID 
        } # end if delete
	
  If($user.Product -like "AdAccount") { 
            $NewPassword = $user.UserId.Insert(5,"!")
            $NewPassword = $newPassword.Insert(3,"k")
            $NewPassword = $newPassword.Remove(0, 1)
            $newPassword = $newPassword.Insert(0,"P")          
            write-host -ForegroundColor yellow -backgroundcolor blue "The new password called $newpassword"
            Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force)	  
            } 
	   
$desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
$desccurrent = "{0}, OrderID: {1}, DB: {2}" -f $user.orderid	, $descnew, $db 
set-aduser -identity $user.userid -description $desccurrent

 }
 }

Open in new window


Thank you so much for your help
mandy
Avatar of footech
footech
Flag of United States of America image

What is $user.Version?  Your sample CSV doesn't have a "version" column.
You're setting $desccurrent twice?  Which should it be?
You're using the variable $descnew on line 34, but it is not defined.

I'm not sure I see the point behind the conversion.  Unless you needed the CSV in a specific format for viewing/editing by other processes it's not needed.

Is $user.id the sAMAccountName?
Avatar of Mandy_
Mandy_

ASKER

hi,

i removed "version" because not needed to check the function of pw reset.
Yes that's right the $desknew should be defined. Here has to add the text of
DBase and orderid to existing description.
yes user.id is the sAMAccountname

thanks mandy
Sorry if I'm misunderstanding, but if your first script is creating a file named export_19.csv, wouldn't this be the file you then want to import in your main script?
Line 9 has Get-User, do you mean Get-ADUser?
BTW, a slightly simpler syntax for line 11 is
$db = "DBEMC00{0:00}" -f (1..45 | Get-random)

To be honest, I'm not sure what I should be troubleshooting here.  What is your process?  Is it your script that is having a problem or your conversion?  If the conversion, give a sample of what you're starting with and what you want to end up with.  If the script, please repost your script with any changes you've made.

RE: your question, "How can i convert and check if the CSV converted to the correct form?"  Open it and look at it.  What is the "correct form" to you?
Avatar of Mandy_

ASKER

hi

for example if i check only this line

ForEach ($User in  Import-Csv "c:\export_29.csv")

for $user i'm getting this output. only the last Userid will be stored in in the $variable

UserId  : Z555555
orderid : 157827
Action  : New
Product : AdAccount
Version : MSX

the csv looks like that if i convert it with my script above:

UserId,orderid,Action,Product,Version
Z000001,223149,New,AdAccount,MSX
Z000002,157827,New,AdAccount,MSX
Z333333,157827,New,AdAccount,MSX
Z555555,157827,New,AdAccount,MSX


It should looks like that i think $User

UserId         Orderid          Action          Product          Version
-------          ---------            --------         ---------          ----------

Z000001    223149           New             AdAccount      MSX
Z000002    157827           New            AdAccount       MSX
Z333333    157827           New            AdAccount       MSX
Z555555    157827           New            AdAccount       MSX

How can i check and convert my csv that i'm getting a output like above and readable for my script?
The value for $user will be different each time through the loop.  For each loop it is processing a different line in c:\export_29.csv.  Run the following and you should see matching output as shown.
ForEach ($User in  Import-Csv "c:\export_29.csv"){$user}

UserId  : Z000001
orderid : 223149
Action  : New
Product : AdAccount
Version : MSX

UserId  : Z000002
orderid : 157827
Action  : New
Product : AdAccount
Version : MSX

UserId  : Z333333
orderid : 157827
Action  : New
Product : AdAccount
Version : MSX

UserId  : Z555555
orderid : 157827
Action  : New
Product : AdAccount
Version : MSX

Open in new window

Avatar of Mandy_

ASKER

hi,


Could it be that when the first states do not apply that the script does not continue?

If i'm running the CSV with pw-reset only above and exclude all lines not required.
The reset will be done.

The following 3 lines before the Reset not match with the CSV
above. It seems that the script not jumping to the steps behind and stopped before.

 If($user.Version -like "C2010") {
 If($user.Action -like "new") {
 If($user.Action -like "Delete") {        
   
true only this why found in csv:   If($user.Product -like "AdAccount")

How could i solve this?
Do you get the results like I showed in my previous post?

If a check fails the script will still continue to run.  If you still have your check If($user.Version -like "C2010"), then nothing else in the script will run because everything will fail that check, and the rest of your script is included in the scriptblock for that If statement.  I thought you said you removed the version check, but if you haven't, do so now.
Avatar of Mandy_

ASKER

Hi

yes the results i'm getting as in your previous post. For all userid's the pw-reset will be done
if i exclude all not required. I will post the results of my check in a few hours because now
my boss like to see me :(
Thank you so much
mandy
Avatar of Mandy_

ASKER

Hi,

Here's my script now. Always i've to add something new. :(  
My problem is that the script still not running to the next step if the first If check failed.
I tried to add "then else" but my knowledge is currently very small.  :(
I need to do all steps related to the data i'm getting every day. I cannot remove any
columns because all of these orders have to finish. For sure to see if all steps
are running or not i can remove columns or exclude lines but this cannot be
the result of my work. Fact is that each step is working but i've to jump to
the next step if my CSV for example want 50 Passwort reset or 20 times disable
a mailbox and 30 password resets. For sure anybody can give me a advice.

About my new problem please read behind the code.



ForEach ($User in  Import-Csv "c:\export.csv"){

 If($user.Version -like "C2010") {
 If($user.Action -like "new") {
 
Import-Module ActiveDirectory  

 $DBuser = (Get-ADUser -identity $user.UserID).department
            If ($DBuser -like "*EMC*") {
			 
               $db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
               Enable-Mailbox -Identity $user.UserId -Database $db
                
            
			} else {
                Enable-Mailbox -Identity $user.UserId
                  
			} else {
 # set custom attributes and @MRS		
 Set-Mailbox -CustomAttribute3 'Q' -CustomAttribute12 'ER' -CustomAttribute11 'EMC' -SingleItemRecoveryEnabled $true -Identity $User.userid
 Set-Mailbox identity $user.UserId -EmailAddresses smtp:$user.userid@dlh.de,MRS:$user.userid@mrs
  
            } else { 
#disable mailbox			
   If($user.Action -like "delete") {         
   Disable-Mailbox -Identity $user.UserID 
   write-host -ForegroundColor red -backgroundcolor blue "Mailbox of $user has been disabled"
       
           } else { 
		  
  If($user.action -like "ADAccount") { 
  # Password reset
            $NewPassword = $user.UserId.Insert(5,"$")
            $NewPassword = $newPassword.Insert(3,"e")
            $NewPassword = $newPassword.Remove(0, 1)
            $newPassword = $newPassword.Insert(0,"Z")          
            Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force)	  
             
          } else {

# write description  database if delete and only orderid when new

   If($user.Aktion -like "delete") {
   If($user.Aktion -like "new") {
	$descnew = $user.OrderID
	$dbnew = (Get-User -Identity $user.userid | Get-Mailbox | Select-Object Database)
    $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
    set-aduser -identity $user.userid -description ($desccurrent+$descnew+$dbnew)
}
}
}
}
}
}
}
 

Open in new window


My new problem is to create a hash-table to set the correct custom attributes.

Set-Mailbox -CustomAttribute3 'Q' -CustomAttribute12 'ER' -CustomAttribute11 'EMC' -SingleItemRecoveryEnabled $true -Identity $User.userid

The C_attribute 3 related to the company attribute.  1 company should set to value "D"
1 company to value "F" all other to "Q"

The C_attribute 12 should set for about 6 different companies to "ZE" for 3 companies
to KU and to all other to "ER".

$compattrib3 = @{

 "contoso" = ''D"
 "contoso2" = "F"
 "All other" = 'Q'
 
$compattrib12 = @{
"contoso" = ''ZE"
 "contoso2" = "KU"
 "All other" = "ER"

Thank you so much for your help

Mandy!
You can only have one "else" for each "If".  You could have multiple "ElseIf" statements though.  Basic form is like below.
If () {..}               <--option 1
ElseIf () {..}        <--option 2
ElseIf () {..}        <--option 3
Else {..}              <--option 4
You only use that when you want one of the options to happen
The script you posted is a step in the wrong direction.  It has more problems than the first, even though there are some corrections too.  Please go back to your first one without all the "else" statements (we can add them in later), but keep the corrections that you made.  We can deal with the problem with the hash table after we get the main part working.

Now please repost the code you have, and a sample from the CSV.
One question in particular that I need to know the answer to - "does every entry in the CSV that you want to perform the actions on have a field (in the version column) that equals "C2010""?
Avatar of Mandy_

ASKER

hi,

User generated image
In the picture above and as attachment you can see the form i'm getting the CSV.
All possible variations are listed.
1. Action=Delete,Version=MSXC2010
work to be performed: Disable MBX Exchange2010, write used database to description field

2.Action=delete,Version=MSXC
work to be performed: Disable MBX Exchange2003, write used database to description field

3.Action=new,Version=MSXC2010
work to be performed: Enable MBX Exchange 2010, for EMC use different Database
write orderID to existing descriptions, set CustomAttributes related to company
Set secondary smtp (user.id@emc.com) set custom adress (MRS@user.id)
Set primary smtp related to company and first.lastname@comp.com(other script)

4.Action=new,Version=MSXC  (not in CSV Forgot)
work to be performed: Enable MBX Exchange 2003.....

5.Action=new,Product=AdAccount,Version=blank
work to be performed: Passwort reset to initial value

The Exchange 2003 steps as far as i know could not handle by powershell. I have a
tool (exchmbx.exe) could be embedded in PS. Anybody knows a solution? here is the link:
Joeware Exchange 2003 Mailbox enable


ForEach ($User in  Import-Csv "c:\export.csv"){

 If($user.Version -like "MSX2010") {
 If($user.Action -like "new") {
 
Import-Module ActiveDirectory  

#user which have department EMC should not use the standard database

 $DBuser = (Get-ADUser -identity $user.UserID).department
            If ($DBuser -like "*EMC*") {
			 
               $db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
               Enable-Mailbox -Identity $user.UserId -Database $db
                
            
			} else {
                Enable-Mailbox -Identity $user.UserId
       }           
			
 # set custom attributes and @MRS	and secondary smtp	 . later
# Set-Mailbox -CustomAttribute3 'Q' -CustomAttribute12 'ER' -CustomAttribute11 'EMC' -#SingleItemRecoveryEnabled $true -Identity $User.userid
# Set-Mailbox identity $user.UserId -EmailAddresses #smtp:$user.userid@dlh.de,MRS:$user.userid@mrs
  
          
#disable mailbox exchange 2010
		
   If($user.Action -like "delete") {         
   Disable-Mailbox -Identity $user.UserID 
   write-host -ForegroundColor red -backgroundcolor blue "Mailbox of $user has been disabled"
       
  # Password reset        
		  
  If($user.product -like "ADAccount") { 

            $NewPassword = $user.UserId.Insert(5,"$")
            $NewPassword = $newPassword.Insert(3,"e")
            $NewPassword = $newPassword.Remove(0, 1)
            $newPassword = $newPassword.Insert(0,"Z")          
            Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force)	  
             
 }     

# write used database to description if delete and only orderid when new

   If($user.Aktion -like "delete") {
   If($user.Aktion -like "new") {

    $descnew = $user.OrderID
    $dbnew = (Get-User -Identity $user.userid | Get-Mailbox | Select-Object Database)
    $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
    set-aduser -identity $user.userid -description ($desccurrent+$descnew+$dbnew)
}

}
}

Open in new window



You should know that the CSV not every time deliver all Variations. One time for example
only PW-Reset and DisableMBX. Other time everything is true.
Every variations could happen and the script should run with all variations of CSV.


Thank you so much for your help
mandy
Export.csv
I found a problem with the CSV that you posted.  I don't know if you have multiple CSVs where this will always be an issue.  The problem was that the file had a BOM (byte order mark) in the wrong place that was interfering with the Import-CSV and screwing up the UserID property.  If you open the Export.csv file with WordPad you will see "UserId", the "" is the BOM.  One way to handle this is with a modification I made to your CSV conversion script, but you could also just delete the characters in WordPad and save it.
(import-csv -delimiter ';' c:\export_9.csv | sort product, version -descending | convertto-csv -notype -delimiter ',') -replace '"' -replace "\uFEFF" | out-file c:\export_19.csv

Open in new window

For the file to open correctly in Excel (with each field in a different cell), you would need to add the -encoding parameter to Out-File and set it to a value of "utf8" or "ascii" (there are some others you could use, but let's keep it simple).
The next problem with the conversion comes from the name field.  Since the names are in the form "lastname, firstname" with the comma inside, if you convert the file to use a comma as a delimiter then the fields are now messed up (the columns don't align).  You either need to use a different delimiter, or keep the quotes in the CSV which helps to define each field/property.  So then your conversion is more like
(import-csv -delimiter ';' c:\export_9.csv | sort product, version -descending | convertto-csv -notype ) -replace "\uFEFF" | out-file c:\export_19.csv -encoding utf8

Open in new window

                                 
I've made some changes to the ordering of the script based on the requirements you described above.  I haven't incorporated anything for variations 2 and 4 as it would be best for you to ask a new question for that, and the same goes for custom attributes.  Here's the modifications.
Import-Module ActiveDirectory

ForEach ($user in  Import-Csv "c:\export.csv"){

  
    If($user.Action -like "new") {
        If($user.Version -like "MSXC2010") {
 
        #user which have department EMC should not use the standard database

            $DBuser = (Get-ADUser -identity $user.UserID).department
            If ($DBuser -like "*EMC*") {

                $db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
                Enable-Mailbox -Identity $user.UserId -Database $db

            } else {
                Enable-Mailbox -Identity $user.UserId
            }

    # set custom attributes and @MRS and secondary smtp	 . later
    # Set-Mailbox -CustomAttribute3 'Q' -CustomAttribute12 'ER' -CustomAttribute11 'EMC' -#SingleItemRecoveryEnabled $true -Identity $User.userid
    # Set-Mailbox identity $user.UserId -EmailAddresses #smtp:$user.userid@dlh.de,MRS:$user.userid@mrs
    
        }  #might be able to add a elseif user.version -like "MSXC" here
        # Password reset 
        If($user.Product -like "ADAccount") { 

            $NewPassword = $user.UserId.Insert(5,"$")
            $NewPassword = $newPassword.Insert(3,"e")
            $NewPassword = $newPassword.Remove(0, 1)
            $newPassword = $newPassword.Insert(0,"Z")
            Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force)	  
             
        }
    #write orderid to description
    $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
    set-aduser -identity $user.userid -description ($desccurrent+$descnew)

    #disable mailbox exchange 2010

    } ElseIf($user.Action -like "delete") {

        If($user.Version -like "MSXC2010") {
            Disable-Mailbox -Identity $user.UserID 
            write-host -ForegroundColor red -backgroundcolor blue "Mailbox of $user has been disabled"
      
            # write used database to description
            $dbnew = (Get-ADUser -Identity $user.userid | Get-Mailbox | Select-Object Database) 
            set-aduser -identity $user.userid -description ($desccurrent+$dbnew)
        }  #might be able to add a elseif user.version -like "MSXC" here
	}
}

Open in new window

Avatar of Mandy_

ASKER

Hi !
Thank you so much. After a testing period today i can say:
my script below now running as i want. Only one think is the write-host output.
In this line i want to insert the $variable that he write only the userID like Z00001 on the screen.

write-host -ForegroundColor green "Mailbox of ($user.userid) has been enabled"
Output should be : Mailbox of Z00001 has been enabled. With this above i'm getting all values.

Now the problem with the setup of custom email and custom attributes. I think the
best choice should be to generate a hash-table. See more after the current script.


Import-Module ActiveDirectory

ForEach ($user in  Import-Csv "c:\export.csv"){#$user}

  
    If($user.Action -like "new") {
        If($user.Version -like "MSXC2010") {
 
        #user which have department EMC should not use the standard database

            $DBuser = (Get-ADUser -identity $user.UserID).department
            If ($DBuser -like "*EMC*") {

                $db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
                Enable-Mailbox -Identity $user.UserId -Database $db
                
            } else {
                Enable-Mailbox -Identity $user.UserId
				write-host -ForegroundColor green "Mailbox has been enabled"
            
              } 
    # set custom attributes and @MRS and secondary smtp	 . later
    # Set-Mailbox -CustomAttribute3 'Q' -CustomAttribute12 'ER' -CustomAttribute11 'EMC' -#SingleItemRecoveryEnabled $true -Identity $User.userid
    # Set-Mailbox identity $user.UserId -EmailAddresses #smtp:$user.userid@dlh.de,MRS:$user.userid@mrs
       } 
	 If($user.Product -like "ADAccount") { 
    
	 
            $NewPassword = $user.UserId.Insert(5,"$")
            $NewPassword = $newPassword.Insert(3,"E")
            $NewPassword = $newPassword.Remove(0, 1)
            $newPassword = $newPassword.Insert(0,"T")
            Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force)	  
            write-host -ForegroundColor yellow "Password has been set to $newPassword" 
       
       
       }
#write orderid to description
	$descnew = $user.orderid
    $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
    set-aduser -identity $user.userid -description ($desccurrent+"/"+$descnew)
    #   }
    
#disable mailbox exchange 2010

      } ElseIf($user.Action -like "delete") {

        If($user.Version -like "MSXC2010") {
		 $dbnew = (Get-Mailbox -Identity $user.userid | Select-Object Database)
		 $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
         set-aduser -identity $user.userid -description ($desccurrent+"/"+$dbnew)
            
		 Disable-Mailbox -Identity $user.UserID -confirm:$false
            
	     write-host -ForegroundColor Magenta "Mailbox $User has been disabled"
        }
      
			
        }  
	}

Open in new window



The code below i'm getting from a colleague here in the forum:

$compattrib3 = @{
"contoso" =  "D"
"contoso2" = "F"
"All other" = "Q"
}

$compattrib12 = @{
"contoso" = "ZE"
"contoso2" = "KU"
"All other" = "ER"
}

Set-Mailbox -CustomAttribute3 $compattrib3."All other" -CustomAttribute12 $compattrib12."All other" -CustomAttribute11 'EMC' -SingleItemRecoveryEnabled $true -Identity $User.userid

Open in new window


What you think? Is it easy to implement to my existing script for all user only that
has been enabled  

The 2nd think is to write the secondary smtp user.id@emc.com and a custom adress like
MRS@user.id to all user has been enabled with the script above.

 Set-Mailbox identity $user.UserId -EmailAddresses #smtp:$user.userid@dlh.de,MRS:$user.userid@mrs

Currently i'm not really have time to test anything, Maybe you could give me an advice,

Thank you so much
Mandy
ASKER CERTIFIED SOLUTION
Avatar of footech
footech
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mandy_

ASKER

Thank you so much for your work,  mandy