Look no further than WatchGuard's Total Security Suite, providing defense in depth against today's most headlining attacks like Petya 2.0 and WannaCry. Keep your organization out of the news with protection from known and unknown threats.
COURSE of the MONTH
15 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Powershell - write User to CSV, explicitly exclude part of the User from any action
Posted on 2013-06-07
Hi,
In the Script posted below i marked the steps where i need 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 at this step?
2. Is there a way to exclude the User which are stored in Database (*WEB*) from the action should taken below, after identify them and write to an external file
or must be this user explicitly exclude from any action before it should taken?
for example: ($_.Version -eq "MSXC2010" -and $_.action -eq "New" -and $user_db -notlike "*WEB*")
appreciate for your help
Mandy
In the Script posted below i marked the steps where i need 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 at this step?
2. Is there a way to exclude the User which are stored in Database (*WEB*) from the action should taken below, after identify them and write to an external file
or must be this user explicitly exclude from any action before it should taken?
for example: ($_.Version -eq "MSXC2010" -and $_.action -eq "New" -and $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 Database WEB should be enable here
Enable-Mailbox -Identity $user.UserId
}
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)
}
}
appreciate for your help
Mandy
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
10 Comments
Active today
Firstly, the code you posted doesn't look like it integrates the corrections several Experts (including me) suggested already. E.g. the "LGA*" switch statement cannot work.
Each switch condition is processed, even if a prior case was hit. To prevent from that, end the code of each case with a break. That makes the switch work more like a cascaded IF.
Each switch condition is processed, even if a prior case was hit. To prevent from that, end the code of each case with a break. That makes the switch work more like a cascaded IF.
Active today
Hi to everyone, hi qlemo
First a big thank you to all the people who have helped me so far.
I'm sorry that i'm not able to integrate all requirements from the beginning.
The complete task is very complex and there are many variations to observed.
I checked every time at home in my virtual environment with exchange2013
every variations of user , databases, departments, password flags, group
membership and and and... You know its not easy to observe everything
at one time. I like a product at the end that makes almost everything
automatically. I dont like to spend a lot of time to fix problems gonna
happen in the live environment. I'm working for a known company
I have to anonymize my code and cannot use realnames of company, groups , user and
so on. For this it could happen that i'm writing one time LCA or DKH or
what else. Names are not important. The error occurs with that i can remove myself.
Pls just pay attention for the questions i'm asking. That are not questions to
fix the complete code itself i need to optimize it with new requirements
as a result of checking and testing.
Ok the switch statement it's already checked and its working. Above i forgot to use
the switch -wildcard ($user.company){ parameter, but thats not my question.
I'm getting always the solution which provide first to me and build in after checking
in test environment. One time the expert resolve my last question not answering
for my next question. What should i do? Private contact as far as i know here
not allowed. I can understand if different handwriting fonts mess up the final result.
Too many cooks spoil the porridge :)
Just pay attention for the questions i'm exactly asking then everything will be fine
Thanks to all again
Mandy
First a big thank you to all the people who have helped me so far.
I'm sorry that i'm not able to integrate all requirements from the beginning.
The complete task is very complex and there are many variations to observed.
I checked every time at home in my virtual environment with exchange2013
every variations of user , databases, departments, password flags, group
membership and and and... You know its not easy to observe everything
at one time. I like a product at the end that makes almost everything
automatically. I dont like to spend a lot of time to fix problems gonna
happen in the live environment. I'm working for a known company
I have to anonymize my code and cannot use realnames of company, groups , user and
so on. For this it could happen that i'm writing one time LCA or DKH or
what else. Names are not important. The error occurs with that i can remove myself.
Pls just pay attention for the questions i'm asking. That are not questions to
fix the complete code itself i need to optimize it with new requirements
as a result of checking and testing.
Ok the switch statement it's already checked and its working. Above i forgot to use
the switch -wildcard ($user.company){ parameter, but thats not my question.
I'm getting always the solution which provide first to me and build in after checking
in test environment. One time the expert resolve my last question not answering
for my next question. What should i do? Private contact as far as i know here
not allowed. I can understand if different handwriting fonts mess up the final result.
Too many cooks spoil the porridge :)
Just pay attention for the questions i'm exactly asking then everything will be fine
Thanks to all again
Mandy
Active today
I understand well that it is not easy for you to provide obfuscated code. Besides, that is not important.
The issue with the code is that we need to understand what it should do, to get the "big picture". So without understanding the code (and that is for sure - I do not understand it), I have posted a recommendation for the second question: use break in each case statement block.
Another recommendation to break that lengthy code into managable parts is to use functions for each task. That way the outer switch is visually protruding, the details are a black box, and we do not need to understand their purpose fully. That has also the advantage that you can test much more easily.
I do not understand your first question - the code seems to do that already?!
The issue with the code is that we need to understand what it should do, to get the "big picture". So without understanding the code (and that is for sure - I do not understand it), I have posted a recommendation for the second question: use break in each case statement block.
Another recommendation to break that lengthy code into managable parts is to use functions for each task. That way the outer switch is visually protruding, the details are a black box, and we do not need to understand their purpose fully. That has also the advantage that you can test much more easily.
I do not understand your first question - the code seems to do that already?!
Active today
hi,
First question: yes i did it already but is it the best way to do that, if i like only
the Userid to an external file?
2nd: Maybe its the better way to put it to my 1st step. At this step i'm converting my
CSV and check the output. I could check also for database web user and just remove that
lines from the CSV. Here's my first step and i tried to implement that.
I did not understand "break in each case statement block"
I'm not expert in powershell but if it to difficult i should accept the error messages
i'm getting if the script try to enable an existing mailbox of this webuser,
but also the custom attributes will be change, an email address will be set
and a password reset will be done.
What you think? I think then its the better way to remove them from the CSV
before. Pls take a look to my code above.
thanks
mandy
First question: yes i did it already but is it the best way to do that, if i like only
the Userid to an external file?
2nd: Maybe its the better way to put it to my 1st step. At this step i'm converting my
CSV and check the output. I could check also for database web user and just remove that
lines from the CSV. Here's my first step and i tried to implement that.
(import-csv -delimiter ';' c:\import.csv | convertto-csv -notype ) -replace "\uFEFF" | out-file c:\export.csv -encoding utf8
ForEach ($User in Import-Csv "c:\export.csv"){$user}
foreach ($line in $user) {
$user_db = (Get-Mailbox -Identity $user.userid).Database
($user_db -like "*WEB*" | Out-File "c:\Temp\webUser.csv") #writing only user.id with column userid
ForEach ($User in Import-Csv "c:\Temp\webUser.csv"
foreach ($line in $user) {
$userid = (Get-Mailbox -Identity $user.userid).alias
(Get-Content "c:\export.csv") -notmatch $userid | Out-File "c:\export2.csv"
I did not understand "break in each case statement block"
I'm not expert in powershell but if it to difficult i should accept the error messages
i'm getting if the script try to enable an existing mailbox of this webuser,
but also the custom attributes will be change, an email address will be set
and a password reset will be done.
What you think? I think then its the better way to remove them from the CSV
before. Pls take a look to my code above.
thanks
mandy
Active today
"break in each case statement block":
It would be much easier to just exclude users from*WEB* db from any action, e.g. with
Import-Module ActiveDirectory
ForEach ($User in Import-Csv "c:\import.csv"){
switch($user){
{$_.Version -eq "MSXC2010" -and $_.aktion -eq "new"} {
# code to execute here
break
}
{$_.Version -eq "MSXC2010" -and $_.action -eq "delete"} {
# ...
break
}
}
It would be much easier to just exclude users from*WEB* db from any action, e.g. with
Import-Module ActiveDirectory
ForEach ($User in Import-Csv "c:\import.csv"){
$user_dept = (Get-ADUser -identity $user.UserID).department
$user_db = (Get-Mailbox -Identity $user.userid).Database
if ($user_db -like '*WEB*') {
# write log files or such
continue # will skip all other code, and continue with the next user
}
# other code
}
Active today
Thank you. thats what i want. But still i have problem to write the user -like database
*web* to an logfile.
{$user_db -like '*WEB*'} | Select Name, Alias | Export-Csv -Encoding 'Unicode' c:\temp\contacts.csv -NoTypeInformation -Append
these isn't functional because the variable stores just the database not alias and name.
*web* to an logfile.
{$user_db -like '*WEB*'} | Select Name, Alias | Export-Csv -Encoding 'Unicode' c:\temp\contacts.csv -NoTypeInformation -Append
these isn't functional because the variable stores just the database not alias and name.
Active today
if ($user_db -like '*WEB*') { $user | select Name, Alias | Export-CSV -NoType -Encoding Unicode C:\Temp\Contacts.csv -Append
Active today
thank you. thats the one. Yes you're right. The -append not working with powershell 2.0
I dont know if the out-file command do it. But what can i use instead in earlier ps versions?
I dont know if the out-file command do it. But what can i use instead in earlier ps versions?
if ($user_db -like '*WEB*') { $user | select userid,name | out-file -filepath c:\webuser.csv -append
Active today
There are several options. You can use the Export-CSV expansion of http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/, or
if ($user_db -like '*WEB*') { $user | select Name, Alias | convertto-csv -NoType | select -skip 1 | out-file -Encoding Unicode C:\Temp\Contacts.csv -Append }
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
Are you ready to implement Active Directory best practices without reading 300+ pages?
You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses
Course of the Month15 days, left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.