We are working on a security review of our Exchange environment. Part of that is to review who has permissions to access what mailboxes.
That's why I put together this little script. Using the Exchange 2007 Management shell, it reads our Exchange 2003 and 2007 environments, processing all the mailboxes. It outputs a single CSV file containing each instance where someone else (not SELF and not a SID) has permissions to a mailbox.
The heart of the script is this line of code. It is the one-liner that reads the mailbox information and filters on the rights I wanted. Without the -and and -notlike operators, I would need to filter through all of the various account permissions.
get-mailbox $thisuserDN | get-adpermission | select user, extendedrights | where {($_.extendedrights -like 'send-as') -and ($_.user -notlike '*SELF*') -and ($_.user -notlike '*S-1-5*')}
Note: It will prompt for the OU of the folder you want. If you don't specify anything and simply hit ENTER, it will use the root of your currently logged on domain.
Takes a CSV file, reads headers and creates mailbox based on values.
Requires two columns in CSV, UserPrincipalname and Name. The UPN can be the SAMAccount value renamed. It will append the variable $UPNDomain to end.
Also requires both Quest ActiveRoles PowerShell tools (to create AD Account) and Exchange 2007 Management tools to set mailbox quota limits.
#CreateMailboxFromCSV.PS1
$file = "c:\List_Of-mailboxes.csv"
$Container = "ou=EricTest,ou=Mailbox Customers,dc=corp,dc=ent"
$UPNDomain = "@example.com"
$strMailServerName = '/o=CA/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=EXCHSrvr1'
$strDatabaseName = 'CN=SG1-PRIV1,CN=SG1,CN=InformationStore,CN=EXCHSrvr1,CN=Servers,CN=First Administrative Group,CN=Administrative Groups,CN=CA,CN=Microsoft Exchange,CN=Services,CN=Configuration,dc=corp,dc=ent'
[array]$csv = import-csv $file
# This script implies that columns "UserPrincipalName", "name" exist in the csv file
if (($csv[0].UserPrincipalName -eq $null) -or ($csv[0].Name -eq $null) -or ($csv[0].Database -eq $null) ) {Throw “Parameter missing… Make sure the CSV file has the following columns: UserPrincipalName, Name, Database, OrganizationalUnit.”}
# Create collection of the commands that we will invoke in the end
[collections.arraylist]$Commands = new-object system.collections.arraylist
for($i = 0; $i -lt $csv.Count; $i ++ ) {
if ($csv[$i].UserPrincipalName.contains("@")) {
[void]$Commands.Add(‘$objNewUser =' + ” new-qaduser -UserPrincipalName `”$($csv[$i].UserPrincipalName)`” -Name `”$($csv[$i].Name)`” -ParentContainer `”$($Container)`”" + ‘ -Password $pwd ‘)
} else {
[void]$Commands.Add(‘$objNewUser =' + ” new-qaduser -UserPrincipalName `”$($csv[$i].UserPrincipalName+$UPNDomain)`” -Name `”$($csv[$i].Name)`” -ParentContainer `”$($Container)`”" + ‘ -Password $pwd ‘)
}
}
# Add other parameters if present in the CSV
if ($csv[0].Alias -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$Commands[$i] = $Commands[$i] + ” -Alias `”$($csv[$i].Alias)`”"
}
}
if ($csv[0].DisplayName -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
#clean up display name value of extra characters
$DNameStr = $csv[$i].DisplayName.Replace('`','')
#write-host $dnamestr
$Commands[$i] = $Commands[$i] + ” -displayname `”$($DNameStr)`”"
}
}
if (($csv[0].FirstName -ne $null) -or ($csv[0].GivenName -ne $null)) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
if ($csv[0].FirstName -ne $null) {
$Commands[$i] = $Commands[$i] + ” -FirstName `”$($csv[$i].FirstName)`”"
} else {
$Commands[$i] = $Commands[$i] + ” -FirstName `”$($csv[$i].GivenName)`”"
}
}
}
if (($csv[0].LastName -ne $null) -or ($csv[0].sn -ne $null)) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
if ($csv[0].LastName -ne $null) {
$Commands[$i] = $Commands[$i] + ” -LastName `”$($csv[$i].LastName)`”"
} else {
$Commands[$i] = $Commands[$i] + ” -LastName `”$($csv[$i].sn)`”"
}
}
}
if ($csv[0].Initials -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$Commands[$i] = $Commands[$i] + ” -Initials `”$($csv[$i].Initials)`”"
}
}
if ($csv[0].telephonenumber -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$Commands[$i] = $Commands[$i] + ” -PhoneNumber `”$($csv[$i].telephonenumber)`”"
}
}
if ($csv[0].faxnumber -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$Commands[$i] = $Commands[$i] + ” -fax `”$($csv[$i].faxnumber)`”"
}
}
if ($csv[0].department -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$Commands[$i] = $Commands[$i] + ” -department `”$($csv[$i].department)`”"
}
}
if (($csv[0].SamAccountName -ne $null) -or ($csv[0].UserPrincipalName -ne $null)) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
if ($csv[0].SamAccountName -ne $null) {
$strSam = $csv[$i].SamAccountName
} else {
$strSam = $csv[$i].UserPrincipalName
$intAt = $strSam.indexof("@")
if ($intAT -ne -1) {
$strSam = $strSam.substring(0,$intAt)
}
}
$Commands[$i] = $Commands[$i] + ” -SamAccountName `”$($strSam)`”"
}
}
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$pwd = new-object Security.SecureString
if ($csv[0].Password -ne $null) {
$csv[$i].Password.ToCharArray() | foreach { $pwd.AppendChar($_) }
} else {
#$csv[$i].UserPrincipalName.ToCharArray() | foreach { $pwd.AppendChar($_)}
$a = new-object random
$b = [string]$a.next()
$B.toCharArray() | foreach { $pwd.AppendChar($_)}
}
write-host $Commands[$i] #varibable is assigned when ran.
Invoke-Expression $Commands[$i]
##http://www.powergui.org/thread.jspa?messageID=11753ⷩ
$LastMbx = $objNewuser.DN
$ldapquery = "LDAP://" + $objNewuser.DN
$newmbx = [ADSI]$ldapquery
$newmbx.mailNickname = $csv[$i].mailnickname
$newmbx.msExchHomeServerName = $strMailServerName
$newmbx.homeMDB = $strDatabaseName
if ($csv[0].mail -ne $null) {
#$strEmailAddress =
$newmbx.Mail = $csv[$i].mail
}
if ($csv[0].proxyaddresses -ne $null) {
$strProxy = $csv[$i].proxyaddresses
$strProxy = $strProxy.Replace(";;",";")
$arrProxy = $strProxy.split(";")
$newmbx.proxyaddresses = $arrProxy
}
$newmbx.setinfo()
}
#### If you don't have the Exchange 2007 management tools, Delete the rest of the script..
"Waiting for Mailbox GUID to populate"
do {
$a = Get-QADUser $lastmbx -IncludedProperties 'msexchmailboxguid' | select msexchmailboxguid
$b = [string]$a.msexchmailboxguid
} while ($b.Length -eq 0)
"I just found:" + $b
if ($csv[0].mdbQuota -ne $null) {
for($i = 0; $i -lt $csv.Count; $i ++ ) {
$csv[$i].UserPrincipalName
get-mailbox $csv[$i].UserPrincipalName | Set-mailbox –prohibitsendquota $csv[$i].mdbQuota -IssueWarningQuota $csv[$i].mbdLimit -UseDatabaseQuotaDefaults $FALSE
}
}
Invariably, when you let your local admins create mailboxes using a web interface, you will get some odd settings.
One of things that I've noticed, when running PowerShelll queries, is all the mailboxes with incorrect quota settings. Users with Send Receive limits lower than the Notification settings. Tired of these alerts, I created the following to dump those mailboxes to a CSV.
Get-Mailbox -Filter "UseDatabaseQuotaDefaults -eq `$false" -ResultSize unlimited | where {($_.prohibitsendquota -lt $_.issuewarningquota) -or ($_.prohibitsendquota -gt $_.prohibitsendreceivequota)} | select servername,Displayname,name, issuewarningquota, prohibitsendquota, prohibitsendreceivequota | Export-Csv -Path "C:\mbxs_with_bad_quotas.csv"
I am hoping that someone might be able to develop a better filter statement. When I tried to use:
-filter {prohibitsendquota -lt issuewarningquota}
it would always error out.
Get-Mailbox : Cannot bind parameter 'Filter' to the target. Exception setting "Filter": "Invalid filter syntax. For a description of the filter parameter syntax see the command help.
"prohibitsendquota -lt issuewarningquota" at position 23."
At line:1 char:20
+ Get-Mailbox -Filter <<<< "prohibitsendquota -lt issuewarningquota"
Now.. Send the CSV to my remote admins, and ask them to help out their users.
:)
I hadn't fully realized the power and simplicty of PowerShell until recently. My manager had asked if a script could be written that would take a CSV file of mailbox names, and hide them or unhide them. My co-worker, well-versed in VBScript, generated a 220 line solution that did just that. When I got into the office, I took that as an invitation to flex the PowerShell
Here is my code:
Import-csv Big-List-of-users.csv | %{get-mailbox $_.emailAddress | set-mailbox -HiddenFromAddressListsEnabled $true/$false}
Some nifty points:
Note: This script utilizes the Exchange 2007 Management Shell, so you'll need to run this from a box with Exchange 2007 System Management tools on it. It does work with Exch2003 mailboxes.
Why such a difference? PowerShell has built-in functions that complete many of the basic tasks that you commonly need to do. For example the code:
import-csv (filename)
does quite a bit of work in only one bit. This reads the file, assigns variables based off the headers, and populates each record with the remainder of the file. Very nice function when you are looking to do a repetitive operation on a series of objects (like modify accounts, or query servers). In VBScript, the code would be something like:
set fso = createobject("scripting.filesystemobject") ' create pointer to file system
set objFile = fso.opentextfile("filename.csv",1) ' create pointer to file
Set MyRSet = CreateObject("ADODB.Recordset") ' initialize a 2d array
If not objfile.atendofstream then
strFirstLine = objfile.readline ' read first line to get headers
arrFirstLine = split(strFirstLine,",") ' create array of header values
For each strHeader in arrFirstLine ' loop through array
MyRSet.Fields.Append strHeader, adVarChar, 255 ' build records
Next
MyRSet.Open ' open connection to data array created
do while not objfile.atendofstream ' read file to populate array
strCSVLine = objFile.readline
arrCSVLine = split(strCSVLine,",")
MyRSet.addnew Array(strFirstLine), arrCSVLine
Loop
End if
set objFile = nothing
I know, the code is much more complicated than necessary for my initial project. It just goes to show how much more efficient PowerShell is for scripting. In VBScript, I would have likely done all my processing inside the file reading loop and never populated the array. My script would have been more tightly focused to that single purpose.
As I learn more about PowerShell, I become more, and more, fond of it. The versatility and simplicity are quickly winning me over and I find myself writing more PS scripts.
I have been tasked with writting a script to analyze our servers on a daily basis. I found this most excellent server inventory script that pulls virtually every conceivable value from a server.
Now, on our Exchange cluster, we've used Volume Mount Points for the resources. This means that a 9gb drive with eight 330gb folders (or mount points) to SAN storage. If I simply pull the drive freespace, I get ~6gb (local hard drive), even though the mount points each show over 100gb free.
So.. this script. It queries the server ($ServerName) and looks up all the mount points, then queries the volumes and returns the current size, free space, and % free for each labeled mount point.
Note (Nov 10, 2008): I cleaned up a warning in the script that popped up a false error when the folder was empty. In addition, I realized the data is more useful to me in a record format, so I have the data returned in an array instead of simple string (CSV) format. Powershell displays the data for one server like this:
| Name | FileSize | FreeSpace | PercFree |
|---|---|---|---|
| I:\SG1\ | 229641617408 | 128873562112 | 36 % |
| I:\Utility\ | 0 | 347809751040 | 100 % |
| I:\SMTPMTA\ | 0 | 53606744064 | 100 % |
#Get-MountPointInfo.PS1 Script
#Eric Woodford
#Scripts@ericwoodford.com
#Nov 11, 2008
#Discover and detail volume mount points on a specified Windows server.
#
function Get-MountPointInfo($ServerName) {
$Summary = @()
$objFSO = New-Object -com Scripting.FileSystemObject
$MountPoints = gwmi -class "win32_mountpoint" -namespace "root\cimv2" -computername $ServerName
$Volumes = gwmi -class "win32_volume" -namespace "root/cimv2" -ComputerName $ServerName| select name, freespace
foreach ($MP in $Mountpoints) {
$MP.directory = $MP.directory.replace("\\","\")
foreach ($v in $Volumes) {
$vshort = $v.name.Substring(0,$v.name.length-1 )
$vshort = """$vshort""" #Make it look like format in $MP (line 11).
if ($mp.directory.contains($vshort)) { #only queries mountpoints that exist as drive volumes no system
$Record = new-Object -typename System.Object
$DestFolder = "\\"+$ServerName + "\"+ $v.name.Substring(0,$v.name.length-1 ).Replace(":","$")
#$destFolder #troubleshooting string to verify building dest folder correctly.
$colItems = (Get-ChildItem $destfolder | where{$_.length -ne $null} |Measure-Object -property length -sum)
#to clean up errors when folder contains no files.
#does not take into account subfolders.
if($colItems.sum -eq $null) {
$fsize = 0
} else {
$fsize = $colItems.sum
}
$TotFolderSize = $fsize + $v.freespace
$percFree = "{0:P0}" -f ( $v.freespace/$TotFolderSize)
$Record | add-Member -memberType noteProperty -name Name -Value $V.name
$Record | add-Member -memberType noteProperty -name FileSize -Value $fsize
$Record | add-Member -memberType noteProperty -name FreeSpace -Value $v.freespace
$Record | add-Member -memberType noteProperty -name PercFree -Value $percFree
$Summary += $Record
}
}
}
return $Summary
}
$ServerName = "YourServerNameHere"
Get-MountPointInfo($ServerName) | convertto-html -title $ServerName > c:\Report-DriveSpace_for_$ServerName.html
This PowerShell script replicates the basic functionality of my Exchange Mailbox export HTA script. Rather quickly, it exports the following values (for all accounts with an email address) :
get-qaduser -dontusedefaultincludedproperties -ObjectAttributes @{mail='*'} -includedproperties sAMAccountName,employeeid,distinguishedname,mail,userprincipalname,givenname,sn,proxyaddresses -searchroot 'corp.ent/SBSUsers/_A - G' -serializevalues -sizelimit 0 | export-csv 'c:\Accounts_mail_A-G.csv'
To run this script, you will need to download and install the latest Quest tools. If you would like to include or change the fields (GivenName, employeeID, etc.) it pulls, don't expect to pull all the user properties as defined by:
Get-QADUser -IncludeAllProperties -ReturnPropertyNamesOnly
I kept trying, but getting only a few fields. Looked like I would get up to the first field that it could not query, then would drop the rest in the request. It appears to want the LDAP equivalents. Maybe I was just phrasing my query wrong, but I still can't pull the 'initials' value for anyone (and I know it is populated.)
I have recently revised this script that exports a few more fields from specific OUs.
Troubleshooting Outlook delegate permissions is a pain. I found the easiest way to get a user's delegates is to create a profile, open their mailbox and check each person.
That's why I created this script. Using the Quest Powershell addons for AD, it reads the delegate permissions for a specified mailbox, then looks up the display name for each delegate or mailbox they are a delegate for.
I'd like to clean up the results a little more, but for now this works nicely.
$entry = Read-Host "Display name of mailbox"
if ($entry -ne $null) {
$a= Get-QADUser $entry -ldapfilter '(mail=*)' -IncludedProperties displayname, publicdelegates, publicdelegatesbl
foreach ($user in $a) {
$user.displayname
"================================="
if ($user.publicdelegates -eq $null) {
Write-host "Has no delegates"
} else
{
Write-host "Delegates:"
$b = $user.publicdelegates;
foreach ($del in $b) {Get-QADUser $del | select-object displayname| sort-object displayname};
" "
}
if ($user.publicdelegatesbl -eq $null) {
Write-host "Is not a delegate"
} else
{
Write-Host 'Is a delegate for:'
$b = $user.publicdelegatesbl;
foreach ($del in $b) {Get-QADUser $del |select-object displayname| sort-object displayname};
" "
}
" "
}
}
A friend is working on a script to pull active LCS accounts from his AD. One last bit of information that he that was troubling him was enabled/disabled AD accounts.
Reading the Scripting Guys article, I found a switch that will tell all disabled AD accounts. Perfect, but just the opposite of what he wanted. Reading deeper, they state that when the bit is set to 2, it shows disabled accounts, so I implied that when it's set to (something else??) 0 (or 1) it must be enabled. Tested 1, nope. Then I found this article that shown that using a NOT statement will return what I was looking for.
This tiny script will query your current AD environment and return all ENABLED accounts in the environment.
get-qaduser -includeallproperties -ldapfilter "(!(userAccountControl:1.2.840.113556.1.4.803:=2))"
The "-includeallproperties" switch is required, otherwise you will get all accounts, and not those that apply to the LDAP filter.
Note, if you haven't already done so, you'll need to download and configure the Quest ActiveRoles Management Shell to run this query. This article helped me setup my environment and includes links to the various tools I use.
I have been attempting to do some cleanup of the Active Directory environment. My latest endeavor is to capture the last logon time from AD and correlate it to active accounts. If they're not logging on, maybe they don't know how, or don't need a mailbox??
My script here, will query all the Exchange servers for user display name and last logon time (LastLogonTime). Since the last logon is a 64-bit integer, you need to do some special handling of it. I found a number of tricks that used bit-level handling to convert the value to a date-time format, but after some pointing and nudging, I realized the simplicity of simply pulling it apart as a string value.
Last bit, I wanted a single CSV file for all my Exchange servers. There is no way I wanted to have 8 CSV files to sort through for all the mailbox information. Even more so, I didn't want to have to merge them all back together, this is a computer I am spending my life in front of.
Finally, I have to thank Microsoft for a great resource for converting my VBScript experience into Powershell. This site is an easy to understand reference for experienced VB scriptter who want to quickly learn PS.
Update (May 2008): In the BETA version of the Quest Active Roles Management shell includes a lastlogon field. This means you can quickly export the data without special formatting (like below).
Get-QADUser -SearchRoot 'your.domain.local/' -IncludeAllProperties | Format-List name, lastlogon*, accountis*
For each account on your domain, this script returns.
Name: guest
LastLogonTimeStamp: May 1, 2008
LastLogon: May 7, 2008
AccountIsDisabled: False
AccountIsLocked: False
...
Re-route this to a CSV file, and you have something to share with the whole family!
Thanks Quest, Nice addition to this tool!
[end update]
# Name: get-lastlogon.ps1
# Author: Eric Woodford - www.ericwoodford.com
# Date: 09/26/2007
# Description: Mailbox last logon information to single CSV file.
# Display Name, Last Logon Time
#
#create header for CSV file.
$CSVFilePath = 'c:\mail_LastLogon.csv'
$strDate = get-date -uformat "%Y/%m/%d"
$strDate | out-file -filepath $CSVFilePath -encoding ascii
$strOutputString = "Display Name,LastLogonTime"
$strOutputString | out-file -filepath $CSVFilePath -encoding ascii -append
#set this to match your environment.
$Computers = get-qadComputer -searchRoot 'corp.ent/Member Servers/Exchange Servers'
foreach ($computer in $computers) {
$users = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer $computer.name | Select-Object MailBoxDisplayName, LastLogonTime
# Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer $computer.name | Select-Object MailBoxDisplayName, LastLogonTime
foreach ($user in $users) {
$date= [string] $user.LastLogonTime
if ($date.length -eq 0) {
$strOutputString = """"+ $user.MailBoxDisplayName + """, N/A"
}
else {
$strOutputString = """"+ $user.MailBoxDisplayName + """," + $date.substring(4,2)+"/"+$date.substring(6,2) +"/"+ $date.substring(0,4)
}
$strOutputString | out-file -filepath $CSVFilePath -encoding ascii -append
}
}