reporting billing account and youngest item

[sandrine_goetha]

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

[ChrisLangevin · Accepted solution]

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

[sandrine_goetha]

Thank you so much Chris this will really help me.

Sandrine

As soon as I could test I'll put is as an answer