Forum Discussion

FLX's avatar
FLX
Level 4
7 years ago
Solved

Get-EVARchive for primarysmtp or LegacyExchangeDN

Hi,

I've just tested the new EV 12.3 and the new introduced remove-evarchive Cmdlet. I want to delete all archives with no connected user mailbox in EV automatically by task. I will get the list from my identity management (smtp address / dispayname).

While testing I've realized, that I can not use the Exchange Displayname as identifier for EV archivename. I need an unique identifier, but however, ArchiveID is dealt only in EV.

Just checked some SQL tables, couldn't find so far this MailboxDN or primarysmtp counterpart there. Do you have any idea?

Easiest way would be that get-evarchive could resolve primarysmtp..however that would be another feature request.

Thanks..

 

  • ChrisLangevin's avatar
    ChrisLangevin
    7 years ago

    You can find the ArchiveId of mailbox archives that have no mailbox associated with them by this SQL query:

    SELECT ArchiveName,VaultEntryId
FROM ArchiveView AS av 
LEFT OUTER JOIN ExchangeMailboxEntry AS eme 
ON eme.DefaultVaultId = av.VaultEntryId
WHERE Type = 9 AND eme.DefaultVaultId IS NULL

    You can use the .NET SqlClient class to run this query from PowerShell and feed its results into the Remove-EVArchive cmdlet. See example script below. Standard disclaimer: provided as-is, no warranty, test thoroughly before you use in production, don't call in a support case for this script, etc.

    #region FunctionDefinitions

Function Get-EVDirectorySQLServer
{
    $OS = Get-WmiObject -Class Win32_OperatingSystem
    If ($OS.OSArchitecture -eq "64-bit") {$EVKeyPath = "HKLM:\SOFTWARE\Wow6432Node\KVS\Enterprise Vault\Directory\DirectoryService"}
    Else {$EVKeyPath = "HKLM:\SOFTWARE\KVS\Enterprise Vault\Directory\DirectoryService"}
    $SQLServer = (Get-ItemProperty $EVKeyPath).'SQLServer Name'
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SQLConnection.ConnectionString = "Server=$SQLServer;database=EnterpriseVaultDirectory;trusted_connection=True"
    Return $SQLConnection
}

Function Query-SQL
{
    Param(
    [Parameter(Mandatory=$true)]
    [System.Data.SqlClient.SqlConnection]
    $Connection
    ,
    [Parameter(Mandatory=$true)]
    [string]
    $QueryText
    )
        
    $SQLStatement = New-Object System.Data.SqlClient.SqlCommand
    $SQLStatement.Connection = $Connection
    $SQLStatement.CommandText = $QueryText
        
    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SQLAdapter.SelectCommand = $SQLStatement
    $Result = New-Object System.Data.DataTable
    $SQLAdapter.Fill($Result) | Out-Null
        
    Return $Result
}

#endregion FunctionDefinitions

#region MainScript

$sqlQuery = 
@"
SELECT VaultEntryId
FROM ArchiveView AS av 
LEFT OUTER JOIN ExchangeMailboxEntry AS eme 
ON eme.DefaultVaultId = av.VaultEntryId
WHERE Type = 9 AND eme.DefaultVaultId IS NULL
"@

$sqlResults = Query-SQL -Connection (Get-EVDirectorySQLServer) -QueryText $sqlQuery
Import-Module EnterpriseVault

Foreach ($rowData in $sqlResults)
{
    Remove-EVArchive -ArchiveId $rowData.VaultEntryId -WhatIf
}

#endregion MainScript

    Once you've tested it and you mean business, remove the -WhatIf parameter from line 56.

     

    --Chris

  • ChrisLangevin's avatar
    ChrisLangevin
    Level 6
    7 years ago

    You can find the ArchiveId of mailbox archives that have no mailbox associated with them by this SQL query:

    SELECT ArchiveName,VaultEntryId
FROM ArchiveView AS av 
LEFT OUTER JOIN ExchangeMailboxEntry AS eme 
ON eme.DefaultVaultId = av.VaultEntryId
WHERE Type = 9 AND eme.DefaultVaultId IS NULL

    You can use the .NET SqlClient class to run this query from PowerShell and feed its results into the Remove-EVArchive cmdlet. See example script below. Standard disclaimer: provided as-is, no warranty, test thoroughly before you use in production, don't call in a support case for this script, etc.

    #region FunctionDefinitions

Function Get-EVDirectorySQLServer
{
    $OS = Get-WmiObject -Class Win32_OperatingSystem
    If ($OS.OSArchitecture -eq "64-bit") {$EVKeyPath = "HKLM:\SOFTWARE\Wow6432Node\KVS\Enterprise Vault\Directory\DirectoryService"}
    Else {$EVKeyPath = "HKLM:\SOFTWARE\KVS\Enterprise Vault\Directory\DirectoryService"}
    $SQLServer = (Get-ItemProperty $EVKeyPath).'SQLServer Name'
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SQLConnection.ConnectionString = "Server=$SQLServer;database=EnterpriseVaultDirectory;trusted_connection=True"
    Return $SQLConnection
}

Function Query-SQL
{
    Param(
    [Parameter(Mandatory=$true)]
    [System.Data.SqlClient.SqlConnection]
    $Connection
    ,
    [Parameter(Mandatory=$true)]
    [string]
    $QueryText
    )
        
    $SQLStatement = New-Object System.Data.SqlClient.SqlCommand
    $SQLStatement.Connection = $Connection
    $SQLStatement.CommandText = $QueryText
        
    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SQLAdapter.SelectCommand = $SQLStatement
    $Result = New-Object System.Data.DataTable
    $SQLAdapter.Fill($Result) | Out-Null
        
    Return $Result
}

#endregion FunctionDefinitions

#region MainScript

$sqlQuery = 
@"
SELECT VaultEntryId
FROM ArchiveView AS av 
LEFT OUTER JOIN ExchangeMailboxEntry AS eme 
ON eme.DefaultVaultId = av.VaultEntryId
WHERE Type = 9 AND eme.DefaultVaultId IS NULL
"@

$sqlResults = Query-SQL -Connection (Get-EVDirectorySQLServer) -QueryText $sqlQuery
Import-Module EnterpriseVault

Foreach ($rowData in $sqlResults)
{
    Remove-EVArchive -ArchiveId $rowData.VaultEntryId -WhatIf
}

#endregion MainScript

    Once you've tested it and you mean business, remove the -WhatIf parameter from line 56.

     

    --Chris