Exporting to CSV based on date and attaching that file to an email

Question

With the following script, it was my intention to achieve the following goals:

1. Create a CSV file from a series of Exchange commands with the file being named based on the date and time.
2. Attach that same file to an email.

The current iteration of my script is as follows:

$ems = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri `
       http://exchangehubcas.mydomain.com/powershell

Import-PSSession $ems -DisableNameChecking

function getRepQ {
    Get-MailboxDatabase | Get-MailboxDatabaseCopyStatus |
        Sort-Object mailboxserver |
        Select-Object -Property mailboxserver, name, status, copyqueuelength,
            replayqueuelength, contentindexstate |
        Export-Csv -Path "c:\admin\scripts\exchange\ExchangeReplication_$((Get-Date).ToString('yyyy-mm-dd_hh-mm-ss')).csv"
}

function qchk {
    $nowfile = getRepQ 
    $data = Get-Content -Path $nowfile | Select-Object -Skip 1 |
            Out-String | ConvertFrom-Csv |
            Select-Object -Property status -Unique
    $repStat = $data.Status
    foreach ($_ in $repStat) {
        if ($_ -eq "Healthy") {
            Send-MailMessage -Attachments $nowfile -From [email protected] `
                -To [email protected] -Subject "Database replication failed" ` 
                -Body "Review attached CSV file for more details." `
                -SmtpServer exchangehubcas.mydomain.com
        }
    }
}

qchk

Ultimately "Healthy" will be changed to "Failed", but while the .CSV file is correctly generated, it fails on the qchk function saying that the path for Get-Content is null.

What am I doing wrong and what is the correct way to accomplish my desired goal?

Answer 1

Your function getRepQ exports the data from Get-MailboxDatabaseCopyStatus to a CSV, but doesn't return anything. Hence nothing is assigned to the variable $nowfile when you call the function in qchk.

Change this:

function getRepQ {
    Get-MailboxDatabase | Get-MailboxDatabaseCopyStatus |
        Sort-Object mailboxserver |
        Select-Object -Property mailboxserver, name, status, copyqueuelength,
            replayqueuelength, contentindexstate |
        Export-Csv -Path "c:\admin\scripts\exchange\ExchangeReplication_$((Get-Date).ToString('yyyy-mm-dd_hh-mm-ss')).csv"
}

to this:

function getRepQ {
    $date = (Get-Date).ToString('yyyy-mm-dd_hh-mm-ss')
    $csv  = "C:\admin\scripts\exchange\ExchangeReplication_$date.csv"

    Get-MailboxDatabase | Get-MailboxDatabaseCopyStatus |
        Sort-Object mailboxserver |
        Select-Object -Property mailboxserver, name, status, copyqueuelength,
            replayqueuelength, contentindexstate |
        Export-Csv -Path $csv

    $csv
}

and the function will both export the data to a CSV and and return the path of that CSV to the caller.