Video Screencast Help

Script for billing account change

Created: 13 Mar 2013 • Updated: 19 Mar 2013 | 2 comments
This issue has been solved. See solution.

Just trying to do a bit of tidying and I've got two things I need to do.

1) Report on all Mailbox Archives with Account unknown SID (Mailbox deleted) that have zero Total items and then delete them.

2) Assign all remaining mailbox Archives with Account unknown SID to one account for 'Bill Usage To'

Is this possible with a SQL Query or EVPM ?

Thanks for any help

Operating Systems:

Comments 2 CommentsJump to latest comment

JesusWept3's picture

go to http://evServer/EnterpriseVault/Usage.asp
then open up the usage by archive name, export to a TSV file

After that you can just import to excel, sort by billing account
then you will have the name of those that have no items

and for those that do have items, you would need to do the following

1. Open the Vault Admin Console
2. Find one user who you wish to change the billing account on
3. Go to the properties and assign the new billing account, note the name of the user
4. Open up SQL Management Studio
5. Run the following query

SELECT A.ArchiveName, T.TrusteeIdentity, T.SID
FROM Archive A, Root R, Trustee T
WHERE A.RootIdentity = R.RootIdentity
     AND R.OwningTrusteeIdentity = T.TrusteeIdentity
     AND A.ArchiveName = 'Your User'

6. Note down the TrusteeIdentity (e.g. 582)
7. Get a list of all the Archive Names from the Usage report
8. Run the following query

UPDATE Root
SET OwningTrusteeIdentity = 582
WHERE R.RootIdentity IN
(SELECT RootIdentity 
 FROM Archive 
 WHERE ArchiveName IN ('User A', 'User B', 'User C', 'User D', 'User E'))

After that, just refresh your vault admin console, and you should no longer have any unknown billing accounts except for the ones you are processing to delete

SOLUTION
JesusWept3's picture

Oh and obviously make backups of your database before making any changes etc