Writing Mailboxstatistics into SQL Server

  • Article

Very often there is a need to keep statistical data longer than expected. The Exchange Cmd-Let Get-mailboxStatistics provides all the data which is for statistics good to know.

But if you want to have data over a longer period of time you have to write the Output data from the Cmd-Let e.g. to a SQL database.

 

First have a look what the cmd-let will do. 

 

 

The following definition is located on SQL Server

Databasename: MailOperation

Servername: WIN81\SQLExpress

The following columns are definied under tbl.MailboxStatistics:

 

The Output of the Cmd-let get-Mailboxstatistics must be converted to the right format for the SQL database MailOperation as shown above.

$MailboxServerStatistics = Get-MailboxStatistics -server $MailboxServer.identity

Once this is all done via the preferred method I want to show in this post how to connect and write to SQL Server instance from powershell.

First establish a Connection to the database:

  $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
  $SQLConnection.ConnectionString = "Data Source=$SQLServer;Integrated Security=true;Initial Catalog=$SQLDatabase;"
  $SQLConnection.Open()
 if($?) {
  Write-Host ("SQL Connection established.")

  Write-Log ("SQL Connection established.")
  Write-Log ("")
  } else {
  Write-Host ("!!! No SQL Connection established. ") -foregroundcolor "yellow" -backgroundcolor "black"
  }

  # Create Command object for sub sequent commands
  $SQLCommand = $SQLConnection.CreateCommand()

     $SQLCommandText = "INSERT INTO tblMailboxStatistic (
              AssociatedItemCount,
              DatabaseName,
              DeletedItemCount,
              DisconnectDate,
              DisconnectReason,
              DisplayName,
              ItemCount,
              LastLoggedOnUserAccount,
              LastLogoffTime,
              LegacyDN,
              MailboxGuid,
              ObjectClass,
              StorageLimitStatus,
              TotalDeletedItemSize,
              TotalItemSize,
              MailboxTableIdentifier,
              ServerName,
              StorageGroupName,
              MoveHistory,
              IsQuarantined,
              IsArchiveMailbox,
              IsValid,
              MapiIdentity,
              OriginatingServer,
              UsrObjectGuid,
              UsrObjectSid) `n "
      $SQLCommandText += "VALUES (`n "
      $SQLCommandText += $MailboxInfoSql.AssociatedItemCount + ", `n "
      $SQLCommandText += $MailboxInfoSql.DatabaseName + ", `n "
      $SQLCommandText += $MailboxInfoSql.DeletedItemCount + ", `n "
      $SQLCommandText += $MailboxInfoSql.DisconnectDate + ", `n "
      $SQLCommandText += $MailboxInfoSql.DisconnectReason + ", `n "
      $SQLCommandText += $MailboxInfoSql.DisplayName + ", `n "
      $SQLCommandText += $MailboxInfoSql.ItemCount + ", `n "
      $SQLCommandText += $MailboxInfoSql.LastLoggedOnUserAccount + ", `n "
      $SQLCommandText += $MailboxInfoSql.LastLogoffTime + ", `n "
      $SQLCommandText += $MailboxInfoSql.LegacyDN + ", `n "
      $SQLCommandText += $MailboxInfoSql.MailboxGuid + ", `n "
      $SQLCommandText += $MailboxInfoSql.ObjectClass + ", `n "
      $SQLCommandText += $MailboxInfoSql.StorageLimitStatus + ", `n "
      $SQLCommandText += $MailboxInfoSql.TotalDeletedItemSize + ", `n "
      $SQLCommandText += $MailboxInfoSql.TotalItemSize + ", `n "
      $SQLCommandText += $MailboxInfoSql.MailboxTableIdentifier + ", `n "
      $SQLCommandText += $MailboxInfoSql.ServerName + ", `n "
      $SQLCommandText += $MailboxInfoSql.StorageGroupName + ", `n "
      $SQLCommandText += $MailboxInfoSql.MoveHistory + ", `n "
      $SQLCommandText += $MailboxInfoSql.IsQuarantined + ", `n "
      $SQLCommandText += $MailboxInfoSql.IsArchiveMailbox + ", `n "
      $SQLCommandText += $MailboxInfoSql.IsValid + ", `n "
      $SQLCommandText += $MailboxInfoSql.MapiIdentity + ", `n "
      $SQLCommandText += $MailboxInfoSql.OriginatingServer + ", `n "
      $SQLCommandText += $MailboxInfoSql.UsrObjectGuid + ", `n "
      $SQLCommandText += $MailboxInfoSql.UsrObjectSid + "`n "
      $SQLCommandText += ")"
   Write-Debug (" SQL Command: " + $SQLCommandText)
            $SQLCommand.CommandText = $SQLCommandText
            $SQLCommand.ExecuteNonQuery()    

 

if (!$SQLConnection -eq $null) {
  $SQLConnection.Close()
}