Report - User with on prem mailbox

Question

I need a Scheduled Task that send an email of a list of all users that have an on prem mailbox. I have found the script to check if the mailbox is on prem but have not found the way to build and email the list. It can be in CSV fomat.

Answer 1

Hello,

Have a look at the following script from our repository: https://www.adaxes.com/script-repositor ... s-s418.htm.

Comments

Looks like the script would build a report of all users with exchange mailboxes. How would I change it to only report on specific office locations and for people with on-prem mailboxes?

e.g. If Office = Orlando or Tampa or Miami & the mail is On-Prem add to list

---

Hello,

Try the updated script below. In the script, the $offices variable specifies the list of possible values for the Office property of the users with on-premise Exchange mailboxes to be included into the report.

$csvFilePath = "C:\Scripts\Report.csv" # TODO: modify me
$offices = @("Orlando", "Tampa", "Miami") # TODO: modify me
$removeCsvFile = $True # TODO: modify me
$propertiesToExport = @("givenName", "middleName", "sn", "title", "co", "mail") # TODO: modify me

# Email message settings
$to = "recipient@domain.com" # TODO: modify me
$subject = "Active Users with on-premise Exchange Mailboxes" # TODO: modify me
$from = "noreply@domain.com" # TODO: Modify me
$mailServer = "mail.domain.com" # TODO: Modify me
$body = "Active Users with on-premise Exchange Mailboxes" # TODO: Modify me

function SearchObjects($filter, $propertiesToLoad)
{
    # Set search parameters
    $searcher = $Context.BindToObject("Adaxes://rootDSE")
    $searcher.SearchFilter = $filter
    $searcher.SearchScope = "ADS_SCOPE_SUBTREE"
    $searcher.PageSize = 500
    $searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
    $searcher.SetPropertiesToLoad($propertiesToLoad)
    $searcher.VirtualRoot = $True

    try
    {
        # Perform the search
        $searchResultIterator = $searcher.ExecuteSearch()
        $searchResults = $searchResultIterator.FetchAll()

        return ,$searchResults
    }
    finally
    {
        # Release resources
        if ($searchResultIterator){ $searchResultIterator.Dispose() }
    }
}

# Build filter
$dateFileTime = [System.DateTime]::UtcNow.ToFileTimeUTC()
$filter = New-Object "System.Text.StringBuilder"
[void]$filter.Append("(&(sAMAccountType=805306368)(|(accountExpires=>$dateFileTime)(accountExpires=0)(accountExpires=9223372036854775807))(!(userAccountControl:1.2.840.113556.1.4.803:=2))(mailNickname=*)(msExchHomeServerName=*)")
[void]$filter.Append("(msExchRecipientTypeDetails=1)")
[void]$filter.Append("(|")
foreach ($office in $offices)
{
    [void]$filter.Append("(physicalDeliveryOfficeName=$office)")
}
[void]$filter.Append("))")

# Find all active users with on-premise mailboxes in certain offices
$searchResults = SearchObjects $filter.ToString() $propertiesToExport

if ($searchResults.Length -eq 0)
{
    $Context.LogMessage("No active users with Exchange mailboxes found", "Information")
    return
}

$records = @($NULL) * $searchResults.Length
for ($i = 0; $i -lt $searchResults.Length; $i++)
{
    $record = @{}
    foreach ($property in $propertiesToExport)
    {
        $record.Add($property, $searchResults[$i].Properties[$property].Value)
    }
    $records[$i] = New-Object PSObject -Property $record
}
$records | Export-Csv -Path $csvFilePath -NoTypeInformation

# Send mail
Send-MailMessage -to $to -From $from -Subject $subject -Body $body -SmtpServer $mailServer -Attachments $csvFilePath

if ($removeCsvFile)
{
    # Remove CSV File
    Remove-Item $csvFilePath -Force
}

---

If I wanted to exclude a list of job titles from this report how would I update the script?