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.
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