Querying Enterprise Vault for stats.

We come across a lot of customers wanting to understand what they have in Enterprise Vault Stores before they start their migrations.   For example, if a customer is looking to migrate away from Enterprise Vault to Mimecast, Proofpoint, etc where they may have been journalling their mail flow through them from a set date, and only want to migrate older data, this script is a way to identify that data.

Some examples

In the following example, I am going to use the cmdlet to query EV with the following criteria :

archiveTypesSelected : User Archives Only, Journal Archives Only, User and Journal Archives Only, File System archives only, Public Folder Archives only,  Or indeed, All EV Data.
LookupEmailInAD : This will look up the aliases in EV and find the SMTP Address of those users. (NOTE: If this option is used, you require the Active Directory PowerShell module to be installed on the server – Add-WindowsFeature RSAT-AD-PowerShell)
splitJournals : This will result in a second CSV file being created. This CSV file will contain each of the journal archives broken down to a month at a time where data is contained.
DateTo : Only data older than the date specified will be returned in the CSV.
pathToReport : This is the path to the CSV file(s) that will be created. If splitJournals is not specified, only one (1) CSV file will be generated, otherwise a second CSV file will be generated with an appended ‘-journal’ to the name specified.

This example will grab only User and Journal data, look up the users email addresses in AD, and also split the journal out into monthly stats.


PS C:\> Get-SmEVStatistics -archiveTypesSelected UserAndJournalOnly -lookupEmailInAD -splitJournals -DateTo '2018-08-01' -pathToReport c:\temp\EVData.csv
Date range specified. Collecting data up to 2018-08-01
Look up users in AD
Journal data will be split into Months
Writing results to File(s)
Querying EVVSExpressVaultStore_1 for UserAndJournalOnly younger than Wednesday, August 1, 2018
Querying EVVSSmJournal_2 for UserAndJournalOnly younger than Wednesday, August 1, 2018

Here’s an example of the output we got from the above cmdlet for your information as well:

The Journal Split CSV looks like this :

Before I paste the script in here, there’s obviously the caveat of :
Only run this if you know what you’re doing. It’s querying SQL, and we don’t want you to cause any issues in your environment.
This script is provided as-is and no Warranties, etc etc etc.

Here’s a download link for the script so you don’t have to suffer through browser formatting.

Get-SmEVStatistics

Here’s the script … Let us know if you find it helpful.

#Requires –Modules ActiveDirectory

function Get-SmEVStatistics {
  #set parameters.
  [CmdletBinding()]
  param(
    [Parameter(Mandatory = $true,Position = 1)]
    [ValidateSet("SMProgress","UsersOnly","JournalOnly","FSAOnly","AllEVData","UserAndJournalOnly","PublicFolders")]
    $archiveTypesSelected,
    [Parameter(Mandatory = $false,Position = 0)]
    $DateTo = (Get-Date -Format "yyyy-MM-dd"),
    [Parameter(Mandatory = $false)]
    $pathToReport,
    [Parameter(Mandatory = $false)]
    [switch]$splitJournals,
    [Parameter(Mandatory = $false)]
    [switch]$lookupEmailInAD = $false
    )

# Initialise Variables
# Comment the next line out and uncomment the following line if you don't have Simply Migrate installed and configured.
$EVSQLConnection = (Get-SmConfig).EVDirectoryDB
# $EVSQLConnection = 'Server=SQLERVERNAME;Database=EnterpriseVaultDirectory;Trusted_Connection=True;'

# Check the EV Directory DB is configured.
if ([String]::IsNullOrEmpty($EVSQLConnection))
{
    write-host "Please configure the Enterprise Vault Directory connection string."
    break
}

# Initialise array.
  [system.collections.arraylist]$RetArray = @()
  [system.collections.arraylist]$jnl = @()
  [system.collections.arraylist]$smJ = @()
  [system.collections.arraylist]$returnArray = @()
  [system.collections.arraylist]$items = @()

# Initialise functions

# Get results from SQL
function getSQLResults ($query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $EVSQLConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $query
    $sqlCmd.CommandTimeout = 1800
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet
}

#archive types that are reported.
  switch ($archiveTypesSelected) 
  {
    'UsersOnly' { $type = "9" }
    'JournalOnly' { $type = "17" }
    'FSAOnly' { $type = "129" }
    'AllEVData' { $type = "ALL" }
    'UserAndJournalOnly' { $type = "9,17" }
    'PublicFolder' { $type = "33" }
  }

# Check if parameters exist.
    if ($PSBoundParameters.ContainsKey('DateTo')) 
    {
      $dateToSQL = 'where DATEDIFF(dd,  ''' + $dateTo + ''', VSAV.IDDateTime) <= 0'
      write-host "Date range specified.  Collecting data up to "$DateTo -ForegroundColor Cyan
    }
    else 
    {
      write-host "No date range specified. No date filter applied." -ForegroundColor Cyan
      $dateToSQL = $null
    }

    If ($PSBoundParameters.ContainsKey('lookupEmailInAD')) 
    {
        write-host "Look up users in AD" -ForegroundColor Green
        $CheckAD = $true
    }
    else 
    {
        write-host "Don't Look up users in AD" -ForegroundColor Yellow
        $CheckAD = $false
    }
    If ($PSBoundParameters.ContainsKey('SplitJournals')) 
    {
        write-host "Journal data will be split into Months" -ForegroundColor Green
        $jsplit = $true
    }
    else 
    {
        write-host "Journal Data will not be split" -ForegroundColor Yellow
        $jsplit = $false
    }
    If ($PSBoundParameters.ContainsKey('PathToReport')) 
    {
        write-host "Writing results to File(s)" -ForegroundColor Green
        $writeToCSV = $true
    }
    else 
    {
         write-host "Writing results to console" -ForegroundColor Yellow
         $writeToCSV = $false
    }


if ($type -eq "ALL") 
{
    $typeQuery = $null
}
else 
{
    if ([string]::IsNullOrEmpty($dateToSQL)) 
    {
        $typeQuery = 'where av.type in (' + $type + ')'
    }
    else 
    {
        $typeQuery = 'and av.type in (' + $type + ')'
    }
}

#SQL query for DB's that contain EV Archives.
$getDatabasesQuery = 'select 
                      vse.DatabaseName as [DBNAME]
                      from ArchiveView av
                      join VaultStoreEntry vse
                      on av.VaultStoreEntryId = vse.VaultStoreEntryId
                      group by 
                      vse.DatabaseName'

#Get the list of databases we wish to query.
$g = @( getSQLResults $getDatabasesQuery)

#iterate through the databases and collect results.
foreach ($archive in $g.Tables) 
{
    $ev1 = ''
    $displayDate = Get-Date $DateTo -Format "D"
    Write-Host "Querying" $archive.DBNAME "for" $archiveTypesSelected" younger than "$displayDate
    $query = 'use ' + $archive.DBNAME + '
        SELECT DISTINCT VSAV.ArchivePointId as [Archive], eme.MbxAlias,eme.MbxNTUser, av.Type, av.ArchiveTypeName,av.ArchiveName,db_name() as [DBName]
        ,count(*) as [Totalitems]
        ,sum (VSP.OriginalSize) as [Size]
        FROM View_Saveset_Archive_Vault VSAV 
        left join View_Saveset_Properties VSP on VSAV.SavesetIdentity = VSP.SavesetIdentity
        left join [EnterpriseVaultDirectory].dbo.ExchangeMailboxEntry eme
        on eme.DefaultVaultId = vsav.ArchivePointId
        left join [EnterpriseVaultDirectory].dbo.archiveview av
        on vsav.ArchivePointId = av.VaultEntryId
        ' + $dateToSQL + $typeQuery + '
        group by VSAV.ArchivePointId,  eme.MbxAlias, av.Type, av.ArchiveName, eme.MbxNTUser,av.ArchiveTypeName
        order by count(*)'
    [void]$items.Add(@(getSQLResults $query))
}

foreach ($UserStat in $items.Tables) 
{
      if (![string]::IsNullOrEmpty($UserStat))
      {
          foreach ($item in $UserStat)
          {
              if (![string]::IsNullOrEmpty($item.MbxNTUser) -and ($CheckAD)) 
              {
                $ev1 = ''
                try 
                {
                    $evaddress = Get-ADUser -Filter "SamAccountName -eq '$($item.MbxNTUser)'" -Properties EmailAddress
                    $ev1 = $evaddress.EmailAddress
                }
                catch 
                {
                    $ev1 = ''
                }
            
              }
              else 
              {
                $ev1 = ''
              }
              $object = New-Object -TypeName System.Object
              $object | Add-Member -Type NoteProperty -Name ArchiveID -Value $item.Archive
              $object | Add-Member -Type NoteProperty -Name mbxAlias -Value $item.MbxAlias
              $object | Add-Member -Type NoteProperty -Name UserName -Value $item.MbxNTUser
              $object | Add-Member -Type NoteProperty -Name email -Value $($ev1)
              $object | Add-Member -Type NoteProperty -Name ArchiveType -Value $item.ArchiveTypeName
              $object | Add-Member -Type NoteProperty -Name ArchiveName -Value $item.ArchiveName
              $object | Add-Member -Type NoteProperty -Name TotalItems -Value $item.TotalItems
              $object | Add-Member -Type NoteProperty -Name ArchiveSize -Value $item.Size
              $RetArray += $object
          }
     }
}

if ($jsplit) 
{
$journalsToSplit = $items.Tables | where {$_.type -eq 17}


    foreach ($journalArchive in $journalsToSplit)
    {

          $displayDate = Get-Date $DateTo -Format "D"
          Write-Host "Querying $($journalArchive.DBName) for Journal breakdown statistics (items older than $displayDate)" -ForegroundColor Yellow
          $JournalSplitQuery = 'Use EnterpriseVaultDirectory
                                    SELECT DISTINCT VSAV.ArchivePointId as [Archive], CAST(YEAR(VSAV.IdDateTime) AS VARCHAR(4)) + ''-'' + right(''00'' + CAST(MONTH(VSAV.IdDateTime) AS VARCHAR(2)), 2) AS [DateRange]
                                    ,count(*) as [ItemsInRange]
                                    ,sum (VSAV.ItemSize) as [SizeOfItems]
                                    FROM ' + $journalArchive.DBName + '.[dbo].View_Saveset_Archive_Vault VSAV 
                                    left join ' + $journalArchive.DBName + '.[dbo].View_Saveset_Properties VSP on VSAV.SavesetIdentity = VSP.SavesetIdentity
                                    where VSAV.ArchivePointId =''' + $journalArchive.Archive + '''
                                    AND  DATEDIFF(dd,  ''' + $dateTo + ''', VSAV.IDDateTime) <= 0
                                    group by CAST(YEAR(VSAV.IdDateTime) AS VARCHAR(4)) + ''-'' + right(''00'' + CAST(MONTH(VSAV.IdDateTime) AS VARCHAR(2)), 2), VSAV.ArchivePointId
                                    order by CAST(YEAR(VSAV.IdDateTime) AS VARCHAR(4)) + ''-'' + right(''00'' + CAST(MONTH(VSAV.IdDateTime) AS VARCHAR(2)), 2)'
          $JournalSplitItems = @( getSQLResults $JournalSplitQuery)
    

        foreach ($jnlitem in $JournalSplitItems.Tables)
        {
            write-host "Journal Splits : " $jnlitem.Archive
            $date = Get-Date ($jnlitem.DateRange)
            $startofmonth = Get-Date $date -Day 1 -Hour 0 -Minute 0 -Second 0
            $soM = Get-Date $startofmonth -Format ("yyyy-MM-dd")
            $endofmonth = (($startofmonth).AddMonths(1).AddSeconds(-1))
            $eoM = Get-Date $endofmonth -Format ("yyyy-MM-dd")
            $jnlobject = New-Object -TypeName System.Object
            $jnlobject | Add-Member -Type NoteProperty -Name ArchiveID -Value $jnlitem.Archive
            $jnlobject | Add-Member -Type NoteProperty -Name DateRange -Value $jnlitem.DateRange
            $jnlobject | Add-Member -Type NoteProperty -Name DateFrom -Value $soM
            $jnlobject | Add-Member -Type NoteProperty -Name DateTo -Value $eoM
            $jnlobject | Add-Member -Type NoteProperty -Name ItemsInRange -Value $jnlitem.ItemsInRange
            $jnlobject | Add-Member -Type NoteProperty -Name SizeOfItems -Value $jnlitem.SizeOfItems
            $jnl += $jnlobject
        }

   }  
}
    Write-Host ""

if ($writeToCSV) 
{
      #Write CSV to specified location if parameter used.
      if (-not ([string]::IsNullOrEmpty($pathToReport))) 
      {
        [VOID]$returnArray.add($RetArray)
            if ($overwritereport -eq 1) 
            {
                $RetArray | Export-Csv $pathToReport -NoTypeInformation -Force
            }
            else 
            {
              $RetArray | Export-Csv $pathToReport -NoClobber -NoTypeInformation
            }
            if ($jsplit) 
            {
                $jnlreportpath = Split-Path $pathToReport
                $jnlReportfilename = [io.path]::GetFileNameWithoutExtension($pathToReport)
                $jnlReport = "$jnlreportpath\$jnlReportfilename-journal.csv"
                [VOID]$returnArray.add($jnl)
                    if ($overwritereport -eq 1) 
                    {
                      Remove-Item $jnlReport
                      $jnl | Export-Csv $jnlReport -NoTypeInformation -Force
                    }
                    else 
                    {
                      $jnl | Export-Csv $jnlReport -NoClobber -NoTypeInformation
                    }
             }
      }
}
else 
{
 [VOID]$returnArray.add($RetArray)
 
 $returnArray | ft
 $jnl | ft
}

}

I hope you’ve found this as useful as I have.

No Comments

Be the first to start a conversation

Leave a Comment