SQL Query for "billing" purposes.

[FreKac2]

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.g. provisioning group membership and a sql query ?

They have not used a special "billing" account previously.

I guess we could use sql query to change the billing account (it's about 11000 archives) but it would get out of date if the user moved to another department/country.

So it's an extra item for the admins to deal with if that is the only option.

Any suggestions appreciated.

BR,

Fredrik

[JesusWept3]

Billing is based on the trustee table which is a. List of SID's so you would have to base all your queries around that

[FreKac2]

So what would be the best approach for it ?

E.g. changing billing account on all the archives (would it work in this scenario for the already archive items).

I guess another question would be how others have solved the issue. Can't think this is the first time a customer didn't use specific billing account from the get go but thought about it after a period of time.

[JesusWept3 · Accepted solution]

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



 

[FreKac2]

Thanks a bunch JesusWept2, will check the query's out.

Didn't expect you to do the job for me.

But I'm not complaining :)

Cheers

Fredrik