Forum Discussion

sandrine_goetha's avatar
7 years ago

reporting billing account and youngest item

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

  • 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,

    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's avatar
      sandrine_goetha
      Level 6

      Thank you so much Chris this will really help me.

      Sandrine

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