Solved

powershell check user if special database then write them to csv

Posted on 2013-06-03
21
346 Views
Last Modified: 2013-06-08
hi

1) I want to filter the user with web-based mbx *WEB* and write them to a csv
2. I want to filter the user with department *EMC* and random database + enable MBX $DB
3. All user not *WEB* and not *EMC* enable MBX without Database parameter
4. All user not *WEB* but *EMC* and all other from point 3  set mailboxadress....

see what i tried

Import-Module ActiveDirectory 
ForEach ($User in  Import-Csv "c:\import.csv"){ #$user}
	switch($user){
		# check columns in CSV           
		{$_.Version -eq "MSXC2010" -and $_.action -eq "new"} {

		
			$WEBuser = (Get-Mailbox -Identity $user.userid | Select-Object Database)
			$DBUSER = $DBuser = (Get-ADUser -identity $user.UserID).department
			
			if($WEBuser -like "*WEB*" | Export-Csv -Path output.csv

			}elseif ($DBuser -like "*EMC*" -and $WEBuser -notlike "*WEB*" {
				$db = "DBEMC00$("{0:00}" -f (1..42 | Get-random))"
			
			}elseif{
				Enable-Mailbox -Identity $user.userid $db
			}
                       else { 
# here should enable all user not WEB and not EMC

                        Enable-Mailbox -Identity $user.UserId  
		}
	}
#here going on all user except the *WEB* user

$mbox = Get-Mailbox -Identity $User.userid
                  $mbox.EmailAddresses = $user.userid + "@dlh.de"
                  #$mbox.EmailAddresses+="MRS:" + $user.userid@mrs
                  Set-Mailbox -Identity $User.userid -EmailAddresses $mbox.Emailaddresses -EmailAddressPolicyEnabled $False

Open in new window

0
Comment
Question by:Mandy_
  • 12
  • 8
21 Comments
 
LVL 39

Expert Comment

by:footech
ID: 39217486
I don't know if all of that is supposed to be actual code, or just pseudo-code to lay out your ideas (or some mix of the two).  It may not be the only problem, but just at first glance I have the following suggestion.  I think you should really read up on the syntax for If/ElseIf/Else statements.  Then take another look at your script.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39218282
hello again @Mandy_
how come u not using the code i provided to you in your previous question?
by closing and awarding points i thought u had the script all work out.
also the requirement here are different from those you've posted in your previous question.
anyway, this is the last script i've posted:
Import-Module ActiveDirectory 
ForEach ($User in  Import-Csv "c:\import1.csv"){ #$user}
	switch($user){
		# check columns in CSV           
		{$_.Version -eq "MSXC2010" -and $_.action -eq "new"} {

			$DBuser = (Get-ADUser -identity $user.UserID).department
			$WEBuser = (Get-Mailbox -Identity $user.userid | Select-Object Database)
			
			
			if($WEBuser -like "*WEB*" -and $DBuser -like "EMC") {
					$db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
			}elseif ($WEBuser -like "*WEB*" -and $DBuser -notlike "*EMC*") {
					$db = "DB00$("{0:00}" -f (20..40 | Get-random))"
			}elseif ($WEBuser -notlike "*WEB*" -and $DBuser -like "*EMC*") {
				$db = "DBEMC00$("{0:00}" -f (1..45 | Get-random))"
			}else{#not WEB or EMC
				$db = $null
			}
			
			if($db -ne $null){
				move-mailbox -Identity $user.userid -TargetDatabase $db -BadItemLimit 15 -PreserveMailboxSizeLimit:$true -Confirm: $false
			}else{
				Enable-Mailbox -Identity $user.userid
			}

			#put here rest of the script
		}
	}
}

Open in new window


in your previous question u mentioned that u got an error when moving the user to other database.
which line causes the error?

regarding to number 1, which user details u would like to export to csv?
regarding 2,3,4, are those all only related to user with web-based mbx *WEB*?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39218301
hi

i rewrote it like below- the if,elseif,else statement should be correct but unfortunately
not did what i like to do. see script above or other thread. Could you pls help me?


ForEach ($User in  Import-Csv "c:\import1.csv"){ #$user}
      switch($user){
            # check columns in CSV          
            {$_.Version -eq "MSXC2010" -and $_.aktion -eq "new"} {

                  $DBuser = (Get-ADUser -identity $user.UserID).department
                  $WEBuser = (Get-Mailbox -Identity $user.userid | Select-Object Database)
                  
                  
                  if($WEBuser -notlike "*WEB*" -and $DBuser -like "EMC") {
                              $db = "DBEMC0$("{0:00}" -f (1..45 | Get-random))"
            Enable-Mailbox -Identity $user.UserId $DB
                  }elseif ($WEBuser -like "*WEB*" | Out-File "c:\Temp\webUser.csv") {
            
                }elseif ($DBuser -notlike "*EMC*" -and $webuser -notlike "$WEB") {
           
            Enable-Mailbox -Identity $user.UserId

                  }
                  else {

                  $mbox = Get-Mailbox -Identity $User.userid
                  $mbox.EmailAddresses = $user.userid + "@emc.com"
                  #$mbox.EmailAddresses+="MRS:" + $user.userid@mrs
                  Set-Mailbox -Identity $User.userid -EmailAddresses $mbox.Emailaddresses -EmailAddressPolicyEnabled $False
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39218306
can u address the questions i asked in my previous post?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39218352
hi sedgwick,

thank you for your answer.

The error i get is not an error in the script. It's because the WEBuser already have
an attached mailbox if they pass the enable-mailbox part.

Also i cannot move as i thought before the webuser directly to a 2010 standard mailbox.
They have to move to other exchange 2003 database and modify some attributes
we do it all with a vbs script.

I like to write the webuser of my csv just to a other csv with userID and name.
That it for now. I have to move them manually with our vbs.

They should not pass any other procedure of our script, just to CSV




regarding to number 1, which user details u would like to export to csv?
webuser - name  and userid
regarding 2,3,4, are those all only related to user with web-based mbx *WEB*?
yes

the database exchange 2003 has everytime the word "WEB" included.

All other user of the csv dont have any mailboxes attached only the webuser.


thanks
mandy
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39218361
u posted the script with  $_.aktion and not  $_.action, is that correct?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39218363
yes it's not important. i will correct that.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39218574
hi sedgwick,

everything ok now or do you need further informations?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39218675
i'm at work so i can't do it now, i'll do it in couple of hours, sorry.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39220231
no problem. thanks for all your help
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39221669
hi sedgwick,

Do you think you could help me today with my problem above?

Best Regards
Mandy
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39221672
yes, i'm checking it right now
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39224729
Do you have still problems to resolve it? Let me know what exactly make problems i will
try to help you. Thanks in advance.
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39224820
@Mandy_
you posts are inconsistent (or i am totally lost).
u said that  2,3,4, are only related to user with web-based mbx *WEB*, however, in your question no#3 said "All user not *WEB* ", so what is correct?
also for the sake of meaningful naming, why do u call database variable $Webuser and department variable $DBuser?
why not something like:
$user_dept = (Get-ADUser -identity $user.UserID).department
$user_db = (Get-Mailbox -Identity $user.userid).Database

Open in new window


u assume that i understand all the use cases involved but i can't get to complete this script without clear description (sorry again).
when u said web-based mbx *WEB*, do u mean user's mailbox database?
can u create use case list which describes what needs to be done in each one?
for instance:
All users with database name like *WEB* should be exported to csv.
the user details are: name, database, userid.
All users with database name like *WEB* and department like *EMC* --> create random database name and add to assign to user mailbox.
All users with database name NOT like *WEB* and department like *EMC* --> do something else.
All users with database name like *WEB* and department NOT like *EMC* --> do something else.
etc...
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39225402
hi

see all the answers above the questions  thanks

Yeah thats correct
All users with database name like *WEB* should be exported to csv.
the user details are: name, database, userid.

no, thats wrong -notlike *WEB*
All users with database name like *WEB* and department like *EMC* --> create random database name and add to assign to user mailbox.

Yeah, thats right. The Web user should be only write to CSV. All other walks the
script down
All users with database name NOT like *WEB* and department like *EMC* --> do something else.


no thats wrong. WEB = Nothing and EMC Random database DBEMC and all other
only to default databases like enable-mailbox without database parameter
 
All users with database name like *WEB* and department NOT like *EMC* --> do something else.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39225413
sorry man i can't understand when u inline my posts.
if u want me to help you please list in bullets all use cases (like i did) without referring to other posts cause they all messed up.
take the time and have it clearly described so i can follow you cause i'm about to giving up.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39226581
Hi,

i will try to create a complete new posting with all informations could help for you to understand. First see an example as the CSV looks like.

For understanding every line is a different order and one or more could be a Mailbox with Outlook Web Access only User (OWA Exch.2003). The order is to just move them
to another 2003 Database for a normal Mailbox in 2003 can be used with outlook Client.

That isn't to perform with our script!!!  What i like to do is only to identify this kind of User in the CSV and write them with their UserID to an external CSV File. Thats it!


All other User in the csv has no Mailbox and no database.
If i also have the Webuser in the part of script where will be enable Mailboxes i'm
getting a error message - They have already a database attached and it cannot
be enable a exchange 2010 user Mailbox for Exchange 2003 OWA user.


The CSV with UserID, Name, OrderID, Action, Product and Version
In the Script posted below i will mark the steps where i want your help e.g. ### Question No. 1###

1. What is the best way to write only the USERIDs of the DB "*WEB*" to an
external CSV?

2. Is there a way to exclude the User Database (*WEB*) from the action should taken
below with all other user?  Or must before every action should taken (Set Custom Attribute,
set Mailaddresses, Password reset)  include this line


if($user_db -notlike "*WEB*" ) {     ????


Import-Module ActiveDirectory 
ForEach ($User in  Import-Csv "c:\import.csv"){ #$user}
switch($user){
		         
{$_.Version -eq "MSXC2010" -and $_.aktion -eq "new"} {

			
$user_dept = (Get-ADUser -identity $user.UserID).department
$user_db = (Get-Mailbox -Identity $user.userid).Database
			
			
if($user_db -notlike "*WEB*" -and $user_dept -like "EMC") {
					
$db = "DBEMC0$("{0:00}" -f (1..43 | Get-random))"
           
Enable-Mailbox -Identity $user.UserId $DB  #here should enable all user in department EMC to Database DBEMC0 - DBEMC043

### Question No. 1 ####
}elseif ($user_db -like "*WEB*" | Out-File "c:\Temp\webUser.csv") {   #here write userids database web to a file

### Question No. 2 ####
		
		    }elseif ($user_dep -notlike "*EMC*" -and $user_db -notlike "*WEB*") {  # all user not department EMC and not Databse WEB should be enable here
            
            Enable-Mailbox -Identity $user.UserId

####################### here should be the end for $user_db database WEB not taken more action

                  }
                  else { 

                  $mbox = Get-Mailbox -Identity $User.userid
                  $mbox.EmailAddresses = $user.userid + "@emc.de"
                  $mbox.EmailAddresses+="MRS:$($user.userid)@MRS"
                  Set-Mailbox -Identity $User.userid -EmailAddresses $mbox.Emailaddresses -EmailAddressPolicyEnabled $False
                  
                     
              
   switch($user.company){
					"LCA*" {Set-Mailbox -Identity $User.userid -CustomAttribute4 'T' -CustomAttribute10 'LCA' -CustomAttribute14 'vcom'}
					"LCG*" {Set-Mailbox -Identity $User.userid -CustomAttribute4 'K' -CustomAttribute10 'LCG' -CustomAttribute14 'vcom'}
					"LCY*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LCY' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
					"LCT*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LCT' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
					"LCP*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LCP' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "LTC*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LTC' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "LVT*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LVT' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "LCN*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'LCN' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "BTL*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'BTL' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "BTM*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'BTM' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "BTS*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'BTS' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "DTN*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'DTN' -CustomAttribute4 'R' -CustomAttribute14 'vcom'}
                    "KPA*" {Set-Mailbox -Identity $User.userid -CustomAttribute10 'KPA' -CustomAttribute4 'R'  -CustomAttribute14 'vcom'}
                    default {Set-Mailbox -Identity $User.userid -CustomAttribute10 'EMC' -CustomAttribute4 'R' -CustomAttribute13 'vcom'}
}

                    $descnew = $user.orderid
                    $desccurrent = (Get-ADUser -identity $user.userid -Properties Description).Description
                    set-aduser -identity $user.userid -description ($desccurrent+"/"+$descnew+" ")
                  }
   			
			}
		{$_.Version -eq "MSXC2010" -and $_.action -eq "delete"} { 
		   
		   $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 exchange 2010
		   	Disable-Mailbox -Identity $user.UserID -confirm:$false
 
           
                  }
                 
                  }
           
              
            
                                # If ($_.Version -eq "MSXC2010" -and $_.action -eq "New") { 
                                {$_.Version -eq "MSXC2010" -and $_.aktion -eq "AdAccount"}  {
                              
                                $NewPassword = $user.UserId.Insert(5,"$")
                                $NewPassword = $newPassword.Insert(3,"L")
                                $NewPassword = $newPassword.Remove(0, 1)
                                $newPassword = $newPassword.Insert(0,"Z")        
                                Set-ADAccountPassword -Identity $user.UserId -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force) 
                                write-host -ForegroundColor magenta "Password of $($user.UserId) has been set to $newPassword"
                              
                                "User {0}  Last PW Reset at {1}" -f $user.userid,((Get-ADUser $user.userid -properties PasswordLastSet).PasswordLastSet)

                                }
                               
                              
                                {$_.Version -eq "MSXC2010" -and $_.aktion -eq "New"} {
                               
                                
                                $PW = (Get-ADUser -identity $user.userid -properties * ).CannotChangePassword
                                If ($PW -eq $false) {
                                $GROUP = (Get-ADUser -identity $user.userid -Properties *).MemberOf | % { ($_ -split ",")[0] } | Where { $_ -like "CN=E*" }
                                If (!($GROUP)) {
                               
                                $NewPassword = $user.userid.Insert(5,"$")
                                $NewPassword = $newPassword.Insert(3,"L")
                                $NewPassword = $newPassword.Remove(0, 1)
                                $newPassword = $newPassword.Insert(0,"Z")        
                                Set-ADAccountPassword -Identity $user.userid -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $newPassword -Force) 
                                write-host -ForegroundColor yellow "Password of $($user.UserId) has been set to $newPassword"
                              
                                "User {0}  Last PW Reset at {1}" -f $user.userid,((Get-ADUser $user.userid -properties PasswordLastSet).PasswordLastSet)
                              
                                }                                          
                           
                                }
                                
                                
                                
                               
                               }
                              
      }
 
}

                
		

Open in new window



I hope you have not been more confused. I did my best.  :)  

Bye Mandy
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39228332
Hi sedgwick,

what you thinking? Are you able to resolve it? Let me know if not then i will close this
thread and open a new one.
thanks
mandy
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39230209
Could you pls told me if you are working on it or not? I like to finish my work asap.
Im still waiting for about 5 days for an answer to optimize my script.
I think everybody should understand what i exactly want. If you dont like to help me
and just kidding me i will find other ways to resolve my problems.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39231877
Its weekend
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39231957
:)   oki
0

Join & Write a Comment

Synchronize a new Active Directory domain with an existing Office 365 tenant
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
To show how to create a transport rule in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Rules tab.:  To cr…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

747 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

13 Experts available now in Live!

Get 1:1 Help Now