How do I export daily server statistics of Exchange 2013 to CSV file from SCOM Database?

Pauciloquent 71 Reputation points
2020-11-17T11:32:32.03+00:00

Hi,

I have taken over SCOM environment where Exchange 2010 was being monitored via SCOM 2007. I upgraded SCOM to 2019 and Exchange to 2016. Also installed the Exchange 2k3 MP.

Now, in their old environment, there was a PowerShell script running that would export Exchange 2010 transport daily statistics to a csv
file. I have looked at the script; it's using the stored procedure called "[Exchange2010].[Transport-Platform-ServerHourly-Report]".

I looked at the stored procedure in SCOM DB, i.e. OperationsManagerDW that is using a table view called "[Exchange2010].[vServerStatisticsDailyV14]"

Now the problem is that new Exchange 2013 Management doesn't come with the old stored procedure and table views. I have already checked the SCOM 2019 database.

I need to convert that PowerShell script for Exchange 2013 but couldn't find the required stored procedure and Table Views.

How can I be sure that required data fields are being monitored by SCOM 2019 for Exchange 2013.

I am tagging both SCOM and SQL community to seek help.

Following are the Column Names of the exported CSV file:

Date
Time
ClientName
ServerName
TotalMessagesDeliveredToLocal
TotalMessagesDeliveredToIntraOrg
TotalMessagesDeliveredToForeignGateway
TotalMessagesDeliveredToForeignAgent
TotalMessagesDeliveredToForeignSmtp
TotalMessagesRoutingFailedForForeign
TotalMessagesAgentFailedForForeign
DSNMessages
EndToEndMessagesInSlaCount
EndToEndMessagesTotalCount
LocalMessagesInSlaCount
LocalMessagesTotalCount

I have pasted the scripts belows.

Following is the PowerShell script that was exporting the data to the csv file:

#==============================================================================
# Exchange 2010 Transport Statistics Daily Export
#==============================================================================
# Read in the parameters (must be first line in the script)
param([string]$cfg = 'DefaultConfig')

# Define the current script revision, for display at the bottom of the report
$CurrentRevision = 1.1

# Get the script path
$CurrentPath = Split-Path $MyInvocation.MyCommand.Path

# Set default variables
$DefaultConfigurationFile = $CurrentPath + "\Configuration.ps1" # For ComputerName and Database Name

$NewLine = [System.Environment]::NewLine

$YesterdaysDate = (Get-Date).AddDays(-1).ToString('yyyyMMdd')


#==========================================================
# Function Locate-FileInHierarchy
#==========================================================
Function Locate-FileInHierarchy
{
    Param (
        [String]$strFileName,
        [Array]$arrAdditionalSubPaths
    )

    $arrSubPaths = $null
    If ($arrAdditionalSubPaths -is [Object]) {
        $arrSubPaths = @()
        $arrSubPaths += $arrAdditionalSubPaths
        $arrAdditionalSubPaths = @()

        ForEach ($objSubPath in $arrSubPaths) {
            If ($objSubPath -is [String]) {
                If ($objSubPath.Length -gt 0) {
                    $arrAdditionalSubPaths += $objSubPath
                }
            }
        }
        $arrAdditionalSubPaths = [Array]($arrAdditionalSubPaths | Sort -unique)
    }

    $SearchPath = $CurrentPath
    $objFile = $null
    If ($strFileName -is [String]) {
        If ($strFileName.Length -gt 0) {
            Do {
                $objFile = Get-ChildItem -path $SearchPath -filter $strFileName

                If ($objFile -eq $null) {
                    If ($arrAdditionalSubPaths -is [Array]) {
                        ForEach ($strSubPath in $arrAdditionalSubPaths) {
                            $objFile = Get-ChildItem -path (Join-Path $SearchPath $strSubPath) -filter $strFileName
                        }
                    }
                }

                $SearchPath = Split-Path -path $SearchPath -parent
            } While (($SearchPath.Length -gt 0) -and ($objFile -eq $null))
        }
    }

    $objFile.FullName
}


#==========================================================
# Load the various include files:
#       Common Functions - Generic constants and functions
#       General Config - Client/site specific constants
#       Script-specific configuration file
#==========================================================
$ConfigFilesLoaded = $true

#==========================================================
# Common Functions - Generic constants and functions

$SearchPath = $CurrentPath
$ConfigFileName = 'CommonFunctions.ps1'
$strConfigFileName = Locate-FileInHierarchy $ConfigFileName
If ($strConfigFileName -ne $null) {
    . $strConfigFileName
    If ($? -eq $false) {
        Write-Host "ERROR: Failed to load Common Functions file `'$ConfigFileName`'" -f Red
        $ConfigFilesLoaded = $false
    }
} Else {
    $ConfigFilesLoaded = $false
}


#==========================================================
# General Configuration - Local configuration variables

$SearchPath = $CurrentPath
$ConfigFileName = 'GeneralConfiguration.ps1'
$strConfigFileName = Locate-FileInHierarchy $ConfigFileName
If ($strConfigFileName -ne $null) {
    . $strConfigFileName
    If ($? -eq $false) {
        Write-Host "ERROR: Failed to load general configuration file `'$ConfigFileName`'" -f Red
        $ConfigFilesLoaded = $false
    }
} Else {
    $ConfigFilesLoaded = $false
}



#==========================================================
# Load script-specific configuration variables
If ($cfg.Length -gt 0) {
   $ConfigFile = $cfg.Trim()

    If ($ConfigFile -ne 'DefaultConfig') {
        If ($ConfigFile.Contains("\") -eq $false) {
            $ConfigFile = "$CurrentPath\$ConfigFile"
        }

        If($ConfigFile.EndsWith(".ps1") -eq $false) {
            Write-Host "ERROR: Configuration file $ConfigFile is not a PowerShell script file. Using default Configuration file." -f Red
            $ConfigFile = "$CurrentPath\NBN Configuration.ps1"
        }

        If((Test-Path -path:$ConfigFile) -eq $false) {
            Write-Host "ERROR: Configuration file $ConfigFile not found. Using default Configuration file." -f Red
            $ConfigFile = "$CurrentPath\NBN Configuration.ps1"
        }
    } Else {
        $ConfigFile = "$CurrentPath\NBN Configuration.ps1"
    }
} Else {
   $ConfigFile = "$CurrentPath\NBN Configuration.ps1"
}

. $ConfigFile
If ($? -eq $false) {
    Write-Host "ERROR: Failed to load script configuration file" -f Red
    $ConfigFilesLoaded = $false
}


#==============================================================================
# Functions
#==============================================================================
#==========================================================
# Function Execute-SQLQuery
#==========================================================
# Return Values:
#       [Dataset] $Dataset
# 
#==========================================================
Function Execute-SQLQuery
{
    Param ( [String]$SQLQuery,
            [String]$SQLServerInstance,
            [String]$Database )


    If ($SQLQuery -is [String]) {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Data Source=$SQLServerInstance;Initial Catalog=$Database;Integrated Security = True"
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        #$SqlCmd.CommandTimeout = 120
        $SqlCmd.CommandTimeout = 900
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet

        $nRecs = $SqlAdapter.Fill($DataSet)
        If ($? -eq $false) {
            out-log "Failed to query database - Connection String : $($SqlConnection.ConnectionString)"
            out-log "SQL Query : $SqlQuery"
        }
    }

    If ($nRecs -gt 0) {
        Return $DataSet
    } Else {
        Return $null
    }
}


#==========================================================
# Internal Script Variables
#==========================================================
$ScriptUserName = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name
$ProcessName = "Exch2010Stats"


#==========================================================
# Log path
#==========================================================
Prepare-Log
If ($LogFile -eq $null) {
    Write-Host "Logging not prepared."
} Else {
    out-log "--- Script Started ---"
}


#==========================================================
# Define the output path and files
#==========================================================
$OutputPath = "$CurrentPath\Reports"

# Create the output directory, if it doesn't exist
$OutputPathExists = $false
If ((Test-Path -path $OutputPath -pathType container) -eq $true)
{
    $OutputPathExists = $true
} Else {
    New-Item -type Directory -path $OutputPath | Out-Null
    If (($?) -eq $false) {
        Write-Host "ERROR: Failed to create the 'Reports' directory."
    } Else {
        $OutputPathExists = $true
    }
}


#==========================================================
# Check all of the Prerequisites
#==========================================================
$Prerequisites = $false

If (($ConfigFilesLoaded -eq $true) -and ($OutputPathExists -eq $true)) {
    $Prerequisites = $true
}


#==============================================================================
# Main Routine
#==============================================================================
If ($Prerequisites -eq $true) {
    out-log "Prerequisites OK - Starting"

    # blnMonthlyReport is set to false in the "NBN Configuration.ps1" file so this will default to daily.
    If ($blnMonthlyReport) {
        $dteEndDate = New-Object DateTime(((Get-Date).Year), ((Get-Date).Month), 1)
    } Else {
        $dteEndDate = New-Object DateTime(((Get-Date).Year), ((Get-Date).Month), ((Get-Date).Day))
    }
    $dteStartDate = $dteEndDate.AddDays(-1 * $intNumOfDays)

    $arrFilesForCompression = @()


#------------------------------------------------------------------------------
# Exchange2010 : Transport-Platform-ServerDaily-Report
#------------------------------------------------------------------------------
    $strReportName = 'AU-NBN Daily Exchange Transport Report - Date'
    $strCSVFileName = "$($OutputPath)\$($strReportName) $($YesterdaysDate).csv"
    out-log "Performing query for $strReportName report"

    $blnOutputFileStarted = $falses

    $intNumOfDays = ($dteEndDate - $dteStartDate).Days
    For ($i=0; $i -le ($intNumOfDays - 1); $i++) {
        $dteReportDate = $dteStartDate.AddDays($i)
        $strReportDate = $dteReportDate.ToString('yyyyMMdd HH:mm:ss')

        $strQuery = "exec [Exchange2010].[Transport-Platform-ServerHourly-Report] @ReportDateTime='$strReportDate'"
        $objDataset = Execute-SQLQuery $strQuery $strDataWarehouseServer $strDataWarehouseDB

        # $arrDataTable += select "TimeStamp,Date,Time,ClientName, ServerName, TotalMessagesDeliveredtoLocal,TotalMessagesDeliveredToIntraOrg,TotalMessagesDeliveredToForeignGateway,TotalMessagesDeliveredToForeignAgent,TotalMessagesDeliveredToForeignSmtp,TotalMessagesRoutingFailedForForeign,TotalMessagesAgentFailedForForeign,DSNMessages,EndToEndMessagesInSlaCount,EndToEndMessagesTotalCount,LocalMessagesInSlaCount,LocalMessagesTotalCount`n"
        # $arrDataTable += Select TimeStamp,Date,Time,ClientName, ServerName, TotalMessagesDeliveredtoLocal,TotalMessagesDeliveredToIntraOrg,TotalMessagesDeliveredToForeignGateway,TotalMessagesDeliveredToForeignAgent,TotalMessagesDeliveredToForeignSmtp,TotalMessagesRoutingFailedForForeign,TotalMessagesAgentFailedForForeign,DSNMessages,EndToEndMessagesInSlaCount,EndToEndMessagesTotalCount,LocalMessagesInSlaCount,LocalMessagesTotalCount `
        # $arrDataTable += select "Time Stamp","Date1","Time1","Client Name", "Server Name", "Total Messages Delivered to Local","Total Messages Delivered to IntraOrg","Total Messages Delivered to ForeignGateway","Total Messages Delivered to ForeignAgent","TotalMessagesDeliveredTo ForeignSmtp","Total MessagesRoutingFailedForForeign","Total MessagesAgentFailedForForeign","DSN Messages","End to End Messages InSlaCount","End to End Messages TotalCount","Local Messages InSlaCount","Local MessagesTotalCount", "`n"

        # Write-Host $strQuery -f Green
        If ($objDataset -ne $null) {
            $objDataTable = $objDataset.Tables[0]
                $arrDataTable = $objDataTable | Select @{Name='Timestamp'; Expression={ $_.Hour.ToString('dd-MMM-yyyy HH:mm') } }, `
                @{Name='Date'; Expression={ $_.Hour.ToString('dd-MMM-yyyy') } }, `
                @{Name='Time'; Expression={ $_.Hour.ToString('HH:mm') } }, `
                @{Name='ClientName'; Expression={ $_.ClientName.Trim().Trim('.') } }, `
                @{Name='ServerName'; Expression={ $_.ServerName.Trim().Trim('.') } }, `
                @{Name='TotalMessagesDeliveredToLocal';Expression={$_.TotalMessagesDeliveredToLocal}}, `
                @{Name='TotalMessagesDeliveredToIntraOrg';Expression={$_.TotalMessagesDeliveredToIntraOrg}}, `
                @{Name='TotalMessagesDeliveredToForeignGateway';Expression={$_.TotalMessagesDeliveredToForeignGateway}}, `
                @{Name='TotalMessagesDeliveredToForeignAgent';Expression={$_.TotalMessagesDeliveredToForeignAgent}}, `
                @{Name='TotalMessagesDeliveredToForeignSmtp';Expression={$_.TotalMessagesDeliveredToForeignSmtp}}, `
                @{Name='TotalMessagesRoutingFailedForForeign';Expression={$_.TotalMessagesRoutingFailedForForeign}}, `
                @{Name='TotalMessagesAgentFailedForForeign';Expression={$_.TotalMessagesAgentFailedForForeign}}, `
                @{Name='DSNMessages';Expression={$_.DSNMessages}}, `
                @{Name='EndToEndMessagesInSlaCount';Expression={$_.EndToEndMessagesInSlaCount}}, `
                @{Name='EndToEndMessagesTotalCount';Expression={$_.EndToEndMessagesTotalCount}}, `
                @{Name='LocalMessagesInSlaCount';Expression={$_.LocalMessagesInSlaCount}}, `
                @{Name='LocalMessagesTotalCount';Expression={$_.LocalMessagesTotalCount}}

            #$strDataTableCSV = $arrDataTable | ConvertTo-CSV -NoTypeInformation
            $strDataTableCSV = $arrDataTable
            $strDataTableCSV | Export-CSV $strCSVFileName -NoType
    }

    If (Test-Path $strCSVFileName) {
        $arrFilesForCompression += ('"' + $strCSVFileName + '"')
    }}

}


#--- Email the CSV file.
"Emailing CSV file..."

                        $MessageSubject = "AU-NBN Daily Exchange Transport Report - Date $YesterdaysDate"
                        $MessageBodyType = 'Text'
                        $MessageBody = 'Data files attached'
                        $MessageAttachment = $strCSVFileName

                        Send-Email
                        Out-Log ("Email sent. Exiting...")



#------------------------------------------------------------------------------
# If logging is configured, then output any script errors and close off the log
#------------------------------------------------------------------------------
If ($LogFile -ne $null) {
    Log-Errors
    out-log "--- Script Completed ---"
}

Here is the Exchange 2010 Stored Procedure:

USE [OperationsManagerDW]
GO

/****** Object:  StoredProcedure [Exchange2010].[Transport-Platform-ServerStatistics-Report]    Script Date: 09/07/2020 21:26:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [Exchange2010].[Transport-Platform-ServerStatistics-Report]
    @IntervalStartDateTime      datetime = NULL,
    @IntervalEndDateTime        datetime = NULL,
    @EndToEndSlaTarget          int = 90,   
    @LocalSlaTarget             int = 30

AS
BEGIN
    SET NOCOUNT ON

    DECLARE
        @ErrorInd               bit
        ,@ErrorMessage          nvarchar(4000)
        ,@ErrorNumber           int
        ,@ErrorSeverity         int
        ,@ErrorState            int
        ,@ErrorLine             int
        ,@ErrorProcedure        nvarchar(256)
        ,@ErrorMessageText      nvarchar(4000)
        ,@DebugLevel            tinyint

    SET @ErrorInd = 0

    BEGIN TRY
        IF @IntervalStartDateTime IS NULL 
            BEGIN
                SET @IntervalStartDateTime = DATEADD(year, - 100, GETUTCDATE())
            END

        IF @IntervalEndDateTime IS NULL 
            BEGIN
                SET @IntervalEndDateTime = GETUTCDATE()
            END

        IF @EndToEndSlaTarget IS NULL 
            BEGIN
                SET @EndToEndSlaTarget = 90
            END

        IF @LocalSlaTarget IS NULL 
            BEGIN
                SET @LocalSlaTarget = 30
            END

                IF OBJECT_ID('tempdb..#ServerStatisticsTemp') IS NOT NULL
                    DROP TABLE #ServerStatisticsTemp

                CREATE TABLE #ServerStatisticsTemp
                (
                    [DateTime]      DateTime,
                    [ClientName]        nvarchar(256),
                    [ServerName]        nvarchar(256),
                    [TotalMessagesDeliveredToLocal] BigInt,
                    [TotalMessagesDeliveredToIntraOrg]  BigInt,
                    [TotalMessagesDeliveredToForeignGateway] BigInt,
                    [TotalMessagesDeliveredToForeignAgent] BigInt,
                    [TotalMessagesDeliveredToForeignSmtp] BigInt,
                    [TotalMessagesRoutingFailedForForeign] BigInt,
                    [TotalMessagesAgentFailedForForeign] BigInt,
                    [DSNMessages] BigInt,
                    [EndToEndMessagesInSlaCount] BigInt,
                    [EndToEndMessagesTotalCount] BigInt,
                    [LocalMessagesInSlaCount] BigInt,
                    [LocalMessagesTotalCount] BigInt
                )

                INSERT INTO #ServerStatisticsTemp
                SELECT 
                    CONVERT(DateTime,SUBSTRING(CONVERT(nvarchar(26),[DateTime],120),1,10),120)
                    ,
                            CASE
                                WHEN CHARINDEX('.',[ClientHostName]) != 0 THEN SUBSTRING([ClientHostName], 1, CHARINDEX('.',[ClientHostName]))
                                ELSE [ClientHostName]
                            END AS [ClientName]
                    ,

                            CASE
                                WHEN CHARINDEX('.',[ServerHostName]) != 0 THEN SUBSTRING([ServerHostName], 1, CHARINDEX('.',[ServerHostName]))
                                ELSE [ServerHostName]
                             END    AS [ServerName]
                    ,SUM(DeliverStoreDriverMessages) AS [TotalMessagesDeliveredToLocal]
                    ,SUM(DeliverSmtpIntraOrgMessages) AS [TotalMessagesDeliveredToIntraOrg]
                    ,SUM(DeliverGatewayMessages)AS [TotalMessagesDeliveredToForeignGateway]
                    ,SUM(DeliverAgentMessages) AS [TotalMessagesDeliveredToForeignAgent]
                    ,SUM(DeliverSmtpForeignMessages) AS [TotalMessagesDeliveredToForeignSmtp]
                    ,SUM(RoutingFailForeignMessages) AS [TotalMessagesRoutingFailedForForeign]
                    ,SUM(AgentFailForeignMessages) AS [TotalMessagesAgentFailedForForeign]
                    ,SUM(DSNMessages) AS [DSNMessages]
                    ,   
                      SUM(
                        CASE 
                            WHEN [EndToEndLatencyPercentileCount] = 0 THEN 1
                            WHEN [EndToEndLatencyPercentile100] < @EndToEndSlaTarget THEN 1
                            WHEN [EndToEndLatencyPercentile99] < @EndToEndSlaTarget THEN .99
                            WHEN [EndToEndLatencyPercentile95] < @EndToEndSlaTarget THEN .95
                            WHEN [EndToEndLatencyPercentile90] < @EndToEndSlaTarget THEN .9
                            WHEN [EndToEndLatencyPercentile80] < @EndToEndSlaTarget THEN .8
                            WHEN [EndToEndLatencyPercentile70] < @EndToEndSlaTarget THEN .7
                            WHEN [EndToEndLatencyPercentile60] < @EndToEndSlaTarget THEN .6
                            WHEN [EndToEndLatencyPercentile50] < @EndToEndSlaTarget THEN .5
                            WHEN [EndToEndLatencyPercentile25] < @EndToEndSlaTarget THEN .25
                            ELSE 0
                        END 
                        * [EndToEndLatencyPercentileCount]
                        )   AS [EndToEndMessagesInSlaCount]
                    ,SUM([EndToEndLatencyPercentileCount])      AS [EndToEndMessagesTotalCount]
                    ,SUM(
                        CASE 
                            WHEN [LocalLatencyPercentileCount] = 0 THEN 1
                            WHEN [LocalLatencyPercentile100] < @LocalSlaTarget THEN 1
                            WHEN [LocalLatencyPercentile99] < @LocalSlaTarget THEN .99
                            WHEN [LocalLatencyPercentile95] < @LocalSlaTarget THEN .95
                            WHEN [LocalLatencyPercentile90] < @LocalSlaTarget THEN .9
                            WHEN [LocalLatencyPercentile80] < @LocalSlaTarget THEN .8
                            WHEN [LocalLatencyPercentile70] < @LocalSlaTarget THEN .7
                            WHEN [LocalLatencyPercentile60] < @LocalSlaTarget THEN .6
                            WHEN [LocalLatencyPercentile50] < @LocalSlaTarget THEN .5
                            WHEN [LocalLatencyPercentile25] < @LocalSlaTarget THEN .25
                            ELSE 0
                        END
                        * [LocalLatencyPercentileCount]
                        )   AS [LocalMessagesInSlaCount]
                    ,SUM([LocalLatencyPercentileCount])     AS [LocalMessagesTotalCount]
                 FROM [Exchange2010].[vServerStatisticsDailyV14] 

                 WHERE ([DateTime] >= @IntervalStartDateTime) AND [DateTime] <= @IntervalEndDateTime
                GROUP BY SUBSTRING(CONVERT(nvarchar(26),[DateTime],120),1,10),[ClientHostName],[ServerHostName]
                ORDER BY SUBSTRING(CONVERT(nvarchar(26),[DateTime],120),1,10)
                SELECT *
                    FROM #ServerStatisticsTemp
            WHERE ([TotalMessagesDeliveredToLocal] > 0
                    OR [TotalMessagesDeliveredToIntraOrg] > 0 
                    OR [TotalMessagesDeliveredToForeignGateway] > 0
                    OR [TotalMessagesDeliveredToForeignAgent] > 0 
                    OR [TotalMessagesDeliveredToForeignSmtp] > 0)            


    END TRY
    BEGIN CATCH
        SELECT
             @ErrorNumber = ERROR_NUMBER()
            ,@ErrorSeverity = ERROR_SEVERITY()
            ,@ErrorState = ERROR_STATE()
            ,@ErrorLine = ERROR_LINE()
            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
            ,@ErrorMessageText = ERROR_MESSAGE()

        SET @ErrorInd = 1
    END CATCH

    IF (@ErrorInd = 1)
    BEGIN
        DECLARE @AdjustedErrorSeverity int

        SET @AdjustedErrorSeverity = CASE
                                        WHEN @ErrorSeverity > 18 THEN 18
                                        ELSE @ErrorSeverity
                                    END

        RAISERROR (777971002, @AdjustedErrorSeverity, 1
            ,@ErrorNumber
            ,@ErrorSeverity
            ,@ErrorState
            ,@ErrorProcedure
            ,@ErrorLine
            ,@ErrorMessageText
            )
    END
END

GO
Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,413 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,797 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments