cancel
Showing results for 
Search instead for 
Did you mean: 

Script for billing account change

Paul_E
Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

2 REPLIES 2

JesusWept3
Level 6
Partner Accredited Certified

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

https://www.linkedin.com/in/alex-allen-turl-07370146

JesusWept3
Level 6
Partner Accredited Certified

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

https://www.linkedin.com/in/alex-allen-turl-07370146