FreKac2
14 years agoLevel 6
SQL Query for "billing" purposes.
Hi all,
Got a request from a customer in regard to getting "reports" from EV so that they can start billing each department/country for the storage used.
Is it possible to do this based on e....
- 14 years ago
Disclaimer: Not supported by symantec, make a full back up of your database
Find the TrusteeIdentity and SID of the user to become the billing account:
Go to an Archive and then set the billing account to the user that you want
After you hit Apply run the following Query from SQL Management Studio
USE EnterpriseVaultDirectory 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'
(your User would be the user that you changed the billing account on, the SID returned will be the SID for the Billing Owner)
Update the Owning Trustee in the database:
Method 1: Changing each archive to use the same Billing Owner
Make sure you noted down the TrusteeIdentity (like 123 as opposed to the full SID)
Run the following to update the users with the new billing owner (Trustee)
USE EnterpriseVaultDirectory UPDATE Root SET OwningTrusteeIdentity = 123 WHERE OwningTrusteeIdentity IS NOT NULL AND Type = 9 AND RootIdentity IN (SELECT R.RootIdentity FROM Root R, Archive A WHERE R.RootIdentity = A.RootIdentity)
The above changes the billing owner for each Root object that belongs to an archive
Method 2: Selectively changing based on Archive Name
USE EnterpriseVaultDirectory UPDATE Root SET OwningTrusteeIdentity = 123 WHERE OwningTrusteeIdentity IS NOT NULL AND Type = 9 AND RootIdentity IN (SELECT R.RootIdentity FROM Root R, Archive A WHERE R.RootIdentity = A.RootIdentity AND A.ArchiveName = 'some User')
Or if you wanted to give everyone the same billing account for those who's name begins with an A
USE EnterpriseVaultDirectory UPDATE Root SET OwningTrusteeIdentity = 123 WHERE OwningTrusteeIdentity IS NOT NULL AND Type = 9 AND RootIdentity IN (SELECT R.RootIdentity FROM Root R, Archive A WHERE R.RootIdentity = A.RootIdentity AND A.ArchiveName LIKE 'a%')
Method 3: Selectively changing based on Active Directory attributes
USE EnterpriseVaultDirectory UPDATE Root SET OwningTrusteeIdentity = 123 WHERE OwningTrusteeIdentity IS NOT NULL AND Type = 9 AND RootIdentity IN (SELECT R.RootIdentity FROM Root R, Archive A, ExchangeMailboxEntry EME WHERE EME.DefaultVaultID = R.VaultEntryID AND R.RootIdentity = A.RootIdentity AND EME.State = 'FL')
The above query will update all those users in Florida with the same billing account
You could also do it based on Department name, such as
USE EnterpriseVaultDirectory UPDATE Root SET OwningTrusteeIdentity = 123 WHERE OwningTrusteeIdentity IS NOT NULL AND Type = 9 AND RootIdentity IN (SELECT R.RootIdentity FROM Root R, Archive A, ExchangeMailboxEntry EME WHERE EME.DefaultVaultID = R.VaultEntryID AND R.RootIdentity = A.RootIdentity AND EME.Department = 'Help Desk')
The downside to whats posted above is that it will only work for enabled users that have an archive relating to an Exchange Mailbox, its possible that you have archives that are no longer associated with an Exchange mailbox (i.e the AD object or mailbox has been deleted so all you have left now is the Archive)
Hope this helps