Tuesday, November 16, 2010

Powershell: Update Timezone Region for Outlook.com Live@Edu Accounts

Here's a quick script to update the Timezone and Language of all your Outlook Live Accounts using remote powershell.

### -----------------------------------------------------------------
### Written by Matt Brown - 11/16/2010
###
### Powershell script requires a csv text file in the following Format:
###  > email
###  > address1@domain.com
###     > user2@domain.com
### -----------------------------------------------------------------

## Setup Vars
$ImportFile = "C:\live-email-list.csv"
$Language = "en-US"
$Timezone = "Pacific Standard Time"
$DateFormat = "M/d/yyyy"
$TimeFormat = "h:mm tt"

## get the email accounts to change
$EmailAccounts = import-csv $ImportFile
Write-Host " Accounts Found in CSV File" $EmailsToCheck.Length

if($EmailsToCheck.Length -gt 0) {
  ## Setup Outlook Session Session and modify accounts
  $LiveCred = Get-Credential
  $loop = 5
  while($loop -gt 0) {
    # this loops handles reconnect if connection to Live fails on first try.
    $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
    if($Session) { 
      $loop = 0
      Import-PSSession $Session
      $EmailAccounts | foreach {
        $Check = Get-MailboxRegionalConfiguration $_.email
        if($Check -ne $Timezone) {
          Write-Host $_.email
          Set-MailboxRegionalConfiguration $_.email -TimeZone $Timezone -Language $Language -DateFormat $DateFormat -TimeFormat $TimeFormat
        }
      }
    } else {
      Write-Host "Session not created... trying again"
      $loop -= 1
    }
  }
}
Remove-PSSession $Session.Id

Monday, May 31, 2010

Auto Logout after 1 hour of usage

I recently needed a script to automatically log the user out after 1 hour of computer usage. I came up with a pretty low tech solution. Set a scheduled task to activate on user login that runs a DOS bat script. The bat script sleeps for 57 minutes and then kicks off a reboot with notice. Note: It requires sleep.exe from the Windows Resource Kit tools.

@ECHO OFF
REM ##########################################################################
REM ### by Matt Brown @ EWU (mbrown@ewu.edu)
REM ###
REM ### This Script should be set to Run at Logon via a scheduled task
REM ### - it should be run as an admnistrator and the users that you
REM ### - want to force a logoff should be standard user accounts. 
REM ###
REM ### - Note: Requires sleep.exe from Windows Resource Kit in system32 dir
REM ##########################################################################

REM ### Sleep script for 57 Minutes (in seconds)
SLEEP 3300

REM ### Set the notice to display to the user when shutdown is initiated
SET NOTICE="Your one hour of time has expired. You will be automatically logged off in 3 Minutes"

REM ### Start a shutdown, complete in 180 seconds and give notice to users
SHUTDOWN /r /t 180 /c %NOTICE% /f

Sunday, February 28, 2010

Remove Terminated User from GAL - Powershell

Quick Powershell script to remove disabled users from the Exchange 2007 Global Address List (GAL) without deleting the account / mailbox. This uses the Quest Active Roles powershell extensions for Active Directory.

### -----------------------------------------------------------------
### Written by Matt Brown - [mbrown@ewu.edu]
###
### Name: Remove Terminated Employees from GAL
###
### Version: v1.0, 02/2010
###
### Info: This script Finds Disabled Users and removes them from the GAL
###
### Requires: 1. Quest Powershell extensions for AD
###
### Note: If you are using Resource Mailboxes that are disabled you
###   will want to directly specify your staff OU.
### -----------------------------------------------------------------
$mydomain = 'domain.company.com/Staff'
get-qaduser -SearchRoot $mydomain -SizeLimit 3000 -Enabled:$false | set-qaduser -objectAttributes @{showinaddressbook=@()}

Friday, February 26, 2010

Exchange 2007 Alias update - Powershell

With a recent migration from an old email system I needed to bring over aliases from the old system that would be grandfathered for those users but not new users. This Powershell script checked the accounts to see if the alias was present and if not added it to the account as an accepted Email Address.


### -----------------------------------------------------------------
### Written by Matt Brown - 01/07/2010
###
### Powershell script to update Exchange Aliases
### from ones found old email system
###
### Requires Exchange Powershell extenstions
###
### Input file should contain csv row for alias and username
### Example: username,alias
### jdoe,jon.doe
### -----------------------------------------------------------------

$thedate = Get-Date -f yyyy-MM-dd_HH-mm
$filename = $thedate + "_output.rtf"
start-transcript -path $filename

# ---------------------------
# Add Quest AD Snapin
# ---------------------------
if(-not (Get-PSSnapin | where { $_.Name -match 'quest.activeroles.admanagement' })) {
add-PSSnapin quest.activeroles.admanagement
}
if(-not (Get-PSSnapin | where { $_.Name -match 'Microsoft.Exchange.Management.PowerShell.Admin' })) {
add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
}

## Grab all the aliases in the file and group in an array by username
$MailAliases = @{}
Import-Csv "test.txt" | foreach {
$MailAliases[$_.Username] += @($_.Alias)
}

## set the domains we want to see for each alias
$Domains = @()
$Domains += "@domain.com"
$Domains += "@sub.domain.com"

## loop through the users and look for each alias
## with each domain in the current list if missing add
## it to the accepted addresses and update the user account
$x = $MailAliases.count
$length = $x
$MailAliases.keys | foreach {
# Get the user account
$User = Get-Mailuser -identity $_
$updateuser = $false
# Check each mail alias in the list
$MailAliases[$_] | foreach {
$ua = $_
$Domains | foreach {
$check = $ua + $_
$needsadd = $true
$User.EmailAddresses | foreach {
if($_.SMTPAddress -eq $check) {
# address found in list, will not be added
$needsadd = $false
}
}
if($needsadd -eq $true) {
# address wasn't found, add to accepted addresses
$User.EmailAddresses += $check
$updateuser = $true
}
}
}
if($updateuser -eq $true) {
# Now Update the User Account with new aliases
#Write-Host $User.Name
$User | Set-Mailuser
}
}
#cleanup
Stop-Transcript

Monday, November 2, 2009

Live @ Edu hotmail active mailboxes

I run a live@edu hotmail system at our school and I needed a way to find out which mailboxes were not longer active (I.E. student hasn't logged in for 365 days). So I put together this little python script to make an SMTP connection on each address load the status into a mysql database.


#######################################
## Written by Matt Brown
## - check hotmail mailbox status active / inactive
## - for live @ edu hotmail accounts
########################################

import smtplib
import fsdb
import time

##-------------------------------------------
def ConnectMySQL(name,debug=0):
fsdb.register_connection(name, 'mydb_ip', 'mydb', 'mydbuser', 'mydbpass')
fsdb.set_debug(debug)
return fsdb
#----------------------------------------------------
def CloseMySQL(name):
fsdb.unregister_connection(name)
#----------------------------------------------------
def RunMySQLQuery(fsdb,query):
return fsdb.query(query,None)
#----------------------------------------------------

count = 0
try:
name ="email"
fsdb = ConnectMySQL(name)
email_accounts = RunMySQLQuery(fsdb,"select id,email from addresses_table WHERE active_mailbox='0'")

s = smtplib.SMTP('pamx1.hotmail.com','25','localhost')
s.ehlo('verify')
s.mail('admin@mydomain.com')

for user in email_accounts:
mbstatus = s.rcpt(user[1])
if mbstatus[0] == 550:
print user, "inactive"
RunMySQLQuery(fsdb,"update addresses_table set active_mailbox=0,mailbox_check=NOW() WHERE id="+str(user[0]))
elif mbstatus[0] == 250:
print user, "active"
RunMySQLQuery(fsdb,"update addresses_table set mailbox_check=NOW() WHERE id="+str(user[0]))
count = count + 1
else:
print mbstatus[0], mbstatus

# hotmail only allows 10 recipiants
if count == 9:
count = 0
time.sleep(2) # pause so we don't get black listed
s.rset
s = smtplib.SMTP('pamx1.hotmail.com','25','localhost')
s.ehlo('verify')
s.mail('admin@mydomain.com')

CloseMySQL(name)

except Exception,e:
CloseMySQL(name)
print e

mysql random password

I needed a quick way to create a random password for a large number of users in mysql.


SELECT SUBSTRING( MD5( RAND() ) FROM 1 FOR 8 ) AS PASSWORD;

Monday, October 12, 2009

Powershell - Terminate Employee in Active Directory

Here's a quick little script to terminate an employee in Active Directory. I'm using the quest AD Powershell command-lets in this script. This uses powershell to disable the AD Account, Change the AD password to a random password, set the description of the account and remove all the group membership from the account.

Input text file looks like this:
empID
08791
08792


Powershell Script:


# ---------------------------
# Add Quest AD Snapin
# ---------------------------
if(-not (Get-PSSnapin | where { $_.Name -match 'quest.activeroles.admanagement' })) {
add-PSSnapin quest.activeroles.admanagement
}
# Load Assembly so we can easily generate a random password.[Reflection.Assembly]::LoadWithPartialName(”System.Web”)

$s = get-credential
connect-qadservice -credential $s -Service "mydomain.com"

Import-Csv "employeeIDList.txt" | foreach {
$user = get-QADObject -SearchRoot 'mydomain.com/People' -Type User -ldapFilter "(employeeID=$_.empID)"
if($user) {
write-host "Disabling " $user.samAccountName
# generate random password
$ranpassword = [System.Web.Security.Membership]::GeneratePassword(10,2)
# Disable User Account
$user | Disable-QADUser
# Set User's Description to Terminated and set a random password
$user | set-QADUser -Description "Terminated" -UserPassword $ranpassword
# Remove User from all Groups (does not include domain users)
$user.memberof | Get-QADGroup | Remove-QADGroupMember -member $user
# Move user to Terminated OU
$user | Move-QADUser -NewParentContainer 'mydomain.com/Terminated'
} else {
write-host $_.empID "not found in Active Directory"
}
$user = $False
}

Saturday, October 10, 2009

SMTP Relay with Exchange 2007 - part 2

Problem: I needed to add new applications / servers to the SMTP Relay Connector and document the additions.

Solution: Powershell script that reads a csv file with needed server / application / admin info. It then adds the IP address to the connector and logs the info to another CSV file.

CSV Files looks like this:
IP,Server,Admin,Application,fromaddr
10.0.0.1,SQL01,John Doe,SQL Mailer,jdoe@mydomain.com

Powershell script

----------------------------------


# Grab all our current connectors - should be 1 per hub server
$Connectors = Get-ReceiveConnector | where { $_.Name -eq "SMTP-Relay" }

# set value to current IP listed in first connector found
$ip = $connectors[0].RemoteIpRanges

# Open the File of IPs to add
$import = Import-Csv "1-NewSMTPRelayAddress.txt"
$updateConnector = $false

# Loop through CSV File
$import | Foreach {
# Set Vars
$newip = $_.ip

# Update Documentation (IP, Server, Admin, Application, Date Access Granted)
$thedate2 = Get-Date -f "yyyy-MM-dd HH:mm"
$out = $newip + "," + $_.Server + "," + $_.Admin + ","
$out += $_.Application + "," + $_.fromaddr + "," + $thedate2 + "`n"
$out | out-file SMTP_Relay_Access.csv -append

# add new ip to the list
$ip += $newip

# set a var so we know we need to update the connectors
$updateConnector = $true
}


if($updateConnector) {
# add those to all the connectors
$Connectors | Set-ReceiveConnector -RemoteIPRanges $ip
Write-host "The Following IPs are in the Connector" $ip

# Log run and clear file
$newname = $thedate + "_IPsAdded.txt"
copy-item -path "1-NewSMTPRelayAddress.txt" -destination "Log\$newname"
"IP,Server,Admin,Application,fromaddr" | out-file 1-NewSMTPRelayAddress.txt
} else {
write-host "No addresses found in the file"
}

Friday, October 9, 2009

SMTP Relay with Exchange 2007 - Part 1

Problem: We needed to allow specific servers / applications to send through our Exchange servers without first authenticating.

Solution: In order to do this we setup a send connector on our Exchange HUB servers that allowed any of the specified IP's to send without authentication.

Powershell to setup the Exchange Connector:


$remoteserver = '10.0.0.1'
$emaildomain = 'mydomain.com'
$HubServers = get-exchangeserver | where { $_.ServerRole -match "HubTransport" }
$HubServers | new-ReceiveConnector -Name 'SMTP-Relay' -Usage 'Custom' -Bindings '0.0.0.0:25' -Fqdn 'mydomain.com' -RemoteIPRanges '10.0.0.1' -AuthMechanism Tls,ExternalAuthoritative -PermissionGroups ExchangeServers

Tuesday, September 29, 2009

Powershell MySQL (insert)

Here's a quick script to write (insert) data into a mysql database using powershell.


function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {

# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
return $conn

}

function WriteMySQLQuery($conn, [string]$query) {

$command = $conn.CreateCommand()
$command.CommandText = $query
$RowsInserted = $command.ExecuteNonQuery()
$command.Dispose()
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}

# setup vars
$user = 'myuser'
$pass = 'mypass'
$database = 'mydatabase'
$MySQLHost = 'database.server.com'

# Connect to MySQL Database
$conn = ConnectMySQL $user $pass $MySQLHost $database

# Read all the records from table
$query = 'INSERT INTO test (id,name,age) VALUES ("1","Joe","33")'
$Rows = WriteMySQLQuery $conn $query
Write-Host $Rows " inserted into database"

This code requires the MySQL ADO Connector. (http://dev.mysql.com/)

Sunday, May 31, 2009

Exchange 2007 Public Folder Setup - powershell

In this example I'm setting up a Public Folder structure for departmental Absence or Leave Calendars, Giving departments an shared calendar that can be used to track vacation, sick leave, holiday's, etc. What I do first is create the top level Public Folder called Absence Calendars. I then grant my username owner rights on that new Public Folder. Now I can do the rest of the setup (create calendar and setup permissions) directly from my outlook client. Note: I could optionally create a department Public Folder under the Absence Calendars folder if needed and then create the calendar under that.


New-PublicFolder -Name 'Absence Calendars' -Path '\' -Server 'mbx01.company.com'
New-PublicFolder -Name 'Accounts Payable' -Path '\Absence Calendars' -Server 'mbx01.company.com'
Add-PublicFolderClientPermission -User username -AccessRights owner -Identity "\Absence Calendars\Accounts Payable"

Once in Outlook open up the Folder List (Go -> Folder List) to see the public Folders (it's at the bottom). You should see the new folder created (in our case Absence Calendars). Right click on the Absence Calendars public folder and go to Create New Folder. In the Create New Folder dialog Choose Calendar Items and a name (I chose AP - Leave).


Now, right click on the new Calendar you just created and select "Change Sharing Permissions...". I usually set the department manager as the Editor and set everybody else to Author. This gives employees the ability to add items to the Calendar and allows the Manager to add / delete all the items. You may want to lock this down further by only allowing the employee's the ability to see the calendar and have the manager add all items once approved. In this case you should set the Default to Reviewer, Anonymous to None, and Manager(s) to Editor.






Monday, April 6, 2009

Removing Other Users Folder from Outlook on Exchange

Question:
How do I remove another user's data folder or inbox that I have showing up in my outlook profile? When I right click and go to close folder it tells me to go to account settings and when I go to account settings it only show's my account.

Answer:
1. Select your Main Outlook Profile (Mailbox - )
2. Right click and go to Properties for "Mailbox - "
3. Under General Tab click on the Advanced Button
4. Click on the Advanced Tab
5. Under Mailboxes, find the mailbox you want to remove and click remove.
6. Click ok and the mailbox should be gone.