Video Screencast Help

EV - Script

Created: 16 Jan 2013 • Updated: 21 Jan 2013 | 5 comments
This issue has been solved. See solution.

I am working on a cleanup task wherein I wanted to delete around 5000 vault accounts. These are users accounts who are no more associated with the organization. How can I do this through scripting?

We have enterprise vault 9.0.2

Discussion Filed Under:

Comments 5 CommentsJump to latest comment

GertjanA's picture

I assume you mean you want to delete the archives? Search the forum for "SQL delete archive"

That will give you what to change in the database. Be aware that the actual delete can take a while.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS

Rob.Wilcox's picture

Remember though that 'playing' with the database is not entirely recommended (by Symantec)

JesusWept3's picture

EVPM does not allow for deletions.

Things EVPM can do are:
 - Set Custom policies to sub folders in exchange mailboxes
 - Import PST files to archives
 - Remove hidden settings
 - Updating Archive and Mailbox Permissions
 - Remove and Reset Archive Permissions

It does not however allow for deletion of items, archives or exports to PST files

JesusWept3's picture

As Rob stated, the updating SQL Methods are absolutely not supported by symantec.
However if you were absolutely adamant you wanted to do the deletes through SQL, it would be

UPDATE Archive
SET ArchiveStatus = 4
WHERE ArchiveName IN ('Joe Smith', 'Tom Walker', 'Jill Rogers')

Then after that you would restart the storage service or go in and out of backup mode

Another way to do it instead of having to specify the archive name could be to delete any archive that doesn't exist in the ExchangeMailboxEntry or isn't linked

So the following query would list the Archives that exist in the Archive Table but not in the ExchangeMailboxEntry table and limit it to list on Exchange Mailboxes only (so you wouldn't get a list of Shared Archives, or journal mailboxes etc)

SELECT ArchiveName
FROM   Archive
WHERE  RootIdentity IN
       (SELECT RootIdentity 
        FROM   Root
        WHERE  Type = 9 -- 9 = Exchange Mailbox
          AND  VaultEntryId NOT IN 
               (SELECT DefaultVaultId
                FROM   ExchangeMailboxEntry))

Then if you were ok with that list you would simply modify the query to do an update

UPDATE Archive
SET    ArchiveStatus = 4
WHERE  RootIdentity IN
       (SELECT RootIdentity 
        FROM   Root
        WHERE  Type = 9 -- 9 = Exchange Mailbox
          AND  VaultEntryId NOT IN 
               (SELECT DefaultVaultId
                FROM   ExchangeMailboxEntry))

But just to put all the warnings on this
1. It is not supported by Symantec at all, the only supported method is through the VAC
2. Absolutely double check, and triple check and have as many eyes on it as possible to ensure the lists match
3. Make thorough backups of the EnterpriseVaultDirectory, Vault Store and fingerprint databases before comitting any changes
4. Make thorough backups of the VaultStores themselves as well before comitting any changes
5. It's going to hammer your SQL Server and Storage where your partitions are stored
6. The only way to stop a delete is to change the archive status from 4 to 1 and even then the items that are lost can only be gotten back from vault store restores from backups
7. Again just to re-iterate , this method is NOT supported by symantec and neither by myself, however a surprising amount of companies do use this method

Another thing you might look in to as well are other paid for options such as Quadrotech's ReVise

And the company I work for, Trace3, are also developing a solution for this as well , so feel free to contact me if you'd like more details on it, but again those are both paid for options.

So in short

1. Either delete through the Vault Admin Console, the only supported method and the only free method
2. Set the archives to delete through SQL updating the archivestatus, absolutely not supported, requires restarts of the storage service, and could also lead to archives being deleted that were not meant to be deleted
3. Use a third party paid for app that accomplishes what you need, provides oversight and auditing reports etc to ensure that the corret archives are to be deleted