Forum Discussion

Tony_Uren's avatar
Tony_Uren
Level 4
16 years ago

Youngest Archived Item

Guys,

Need to pull the youngest archived item from all vaults.  Have found a few options on this forum but they don't seem to relate to my version ie. different table names.

I am using EV 7.5 SP3

thanks
Enterprise Vault
Information Governance
  • John_Chisari's avatar
    John_Chisari
    16 years ago
    It's a SQL View in the EVDirectory database.

    Uses this sql query:-

    SELECT VaultEntryId, Type, RootIdentity, ArchiveName, ArchiveStatus, Structured, IndexingLevel, LocaleId, COUNT(DISTINCT IndexVolumeSetId)
    AS CountIndexVolumeSets, COUNT(RootIdentity) AS CountIndexVolumes, COUNT(NULLIF (Failed, 0)) AS CountFailedIndexVolumes,
    COUNT(NULLIF (Offline, 0)) AS CountOfflineIndexVolumes, COUNT(NULLIF (Rebuilding, 0)) AS CountRebuildingIndexVolumes,
    MIN(OldestArchivedDateUTC) AS OldestArchivedDateUTC, MAX(YoungestArchivedDateUTC) AS YoungestArchivedDateUTC,
    NULLIF (MIN(ISNULL(OldestItemDateUTC, '17530101')), '17530101') AS OldestItemDateUTC, NULLIF (MAX(ISNULL(YoungestItemDateUTC, '99991231')),
    '99991231') AS YoungestItemDateUTC, MAX(ISNULL(HighestItemSequenceNumber, 0)) AS HighestItemSequenceNumber, SUM(IndexedItems)
    AS IndexedItems, SUM(FailedItems) AS FailedItems, IndexItemsMode, ConvertedContentMode, IgnoreIndexingPolicy
    FROM dbo.IndexVolumeView
    GROUP BY VaultEntryId, Type, RootIdentity, ArchiveName, ArchiveStatus, Structured, IndexingLevel, LocaleId, IndexItemsMode, ConvertedContentMode,
    IgnoreIndexingPolicy


  • John_Chisari's avatar
    John_Chisari
    Level 6
    16 years ago
     Try this one, will give you everything, change it to what columns you want.

    Select * from IndexView
  • Tony_Uren's avatar
    Tony_Uren
    Level 4
    16 years ago
    Thanks, but this is where the problem lies - where is IndexView ?  I have looked at the list of tables in Directory and MailboxVaultStore databases and cannot see this.
  • John_Chisari's avatar
    John_Chisari
    Level 6
    16 years ago
    It's a SQL View in the EVDirectory database.

    Uses this sql query:-

    SELECT VaultEntryId, Type, RootIdentity, ArchiveName, ArchiveStatus, Structured, IndexingLevel, LocaleId, COUNT(DISTINCT IndexVolumeSetId)
    AS CountIndexVolumeSets, COUNT(RootIdentity) AS CountIndexVolumes, COUNT(NULLIF (Failed, 0)) AS CountFailedIndexVolumes,
    COUNT(NULLIF (Offline, 0)) AS CountOfflineIndexVolumes, COUNT(NULLIF (Rebuilding, 0)) AS CountRebuildingIndexVolumes,
    MIN(OldestArchivedDateUTC) AS OldestArchivedDateUTC, MAX(YoungestArchivedDateUTC) AS YoungestArchivedDateUTC,
    NULLIF (MIN(ISNULL(OldestItemDateUTC, '17530101')), '17530101') AS OldestItemDateUTC, NULLIF (MAX(ISNULL(YoungestItemDateUTC, '99991231')),
    '99991231') AS YoungestItemDateUTC, MAX(ISNULL(HighestItemSequenceNumber, 0)) AS HighestItemSequenceNumber, SUM(IndexedItems)
    AS IndexedItems, SUM(FailedItems) AS FailedItems, IndexItemsMode, ConvertedContentMode, IgnoreIndexingPolicy
    FROM dbo.IndexVolumeView
    GROUP BY VaultEntryId, Type, RootIdentity, ArchiveName, ArchiveStatus, Structured, IndexingLevel, LocaleId, IndexItemsMode, ConvertedContentMode,
    IgnoreIndexingPolicy


  • Paul_Grimshaw's avatar
    Paul_Grimshaw
    Level 6
    16 years ago
    It is a SQL view that resides within the EnterpriseVaultDirectory database. So to cut out all of the fluff of running select  * from indexview. You could run the following:-

    Use EnterpriseVaultDirectory
    select archivename, YoungestItemDateUTC from Indexview

    or if you wanted the youngest archived date it would be:-

    Use EnterpriseVaultDirectory
    select archivename, YoungestArchivedDateUTC from Indexview
  • Tony_Uren's avatar
    Tony_Uren
    Level 4
    16 years ago
    Much better - thanks to you both.

    I am obviously not an SQL guru !!!
  • Paul_Grimshaw's avatar
    Paul_Grimshaw
    Level 6
    16 years ago
    If you do not mind and it is possible could you put the solution against John please instead of me as he really did the work here.