06-21-2018 12:42 AM
Hello,
I know how to find out in the reports which billing account is applied on the archive.
I also know still in the reports how to find the youngest archived item.
What I would like to do i to find out via reports or SQL query the "billing account" AND the "youngest archived date" for a specific archive
Can somebody help me on this one ?
Thank you
Sandrine
Solved! Go to Solution.
06-21-2018 10:31 AM - edited 06-21-2018 10:45 AM
Sandrine,
The info you want is in the ArchiveView view and IndexVolume table. You can join them on RootIdentity like this:
SELECT ArchiveName , VaultEntryId AS ArchiveId , SID AS BillingAccount , OldestItemDateUTC , YoungestItemDateUTC , OldestArchivedDateUTC , YoungestArchivedDateUTC FROM ArchiveView JOIN IndexVolume ON IndexVolume.RootIdentity = ArchiveView.RootIdentity
Example results:
(I included all the Oldest and Youngest dates but obviously you can cut out what you don't need.)
Since the Billing Account is only stored as a SID in SQL, you would need to run an AD lookup to convert that to a more legible username. I have attached a PS function that does this.
Example results:
Hope that helps!
--Chris
06-21-2018 10:31 AM - edited 06-21-2018 10:45 AM
Sandrine,
The info you want is in the ArchiveView view and IndexVolume table. You can join them on RootIdentity like this:
SELECT ArchiveName , VaultEntryId AS ArchiveId , SID AS BillingAccount , OldestItemDateUTC , YoungestItemDateUTC , OldestArchivedDateUTC , YoungestArchivedDateUTC FROM ArchiveView JOIN IndexVolume ON IndexVolume.RootIdentity = ArchiveView.RootIdentity
Example results:
(I included all the Oldest and Youngest dates but obviously you can cut out what you don't need.)
Since the Billing Account is only stored as a SID in SQL, you would need to run an AD lookup to convert that to a more legible username. I have attached a PS function that does this.
Example results:
Hope that helps!
--Chris
06-21-2018 10:59 PM
Thank you so much Chris this will really help me.
Sandrine
As soon as I could test I'll put is as an answer