11-30-2010 05:24 AM
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
Solved! Go to Solution.
11-30-2010 07:48 AM
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)
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)
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')
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%')
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')
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')
11-30-2010 05:45 AM
11-30-2010 06:24 AM
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.
11-30-2010 07:48 AM
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)
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)
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')
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%')
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')
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')
12-02-2010 09:07 AM
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