SQL query

[GTK]

hi

we are on EV 9 sp4. I have the SQL query below. This gives me archive name and last time an archive was modified. I would like also to include the mbxalias info from the 'exchangemailboxentry' table. I believe i need to add a join query. Can anyone advise please??

SQL Query:

select EVDA.Archivename,round(cast(AP.ArchivedItemsSize as float)/1024,2) as 'size of archive in MB', Ap.ArchivedItems as 'no of archived items', AP.modifieddate from archivepoint AP

inner join enterprisevaultdirectory.dbo.root EVDR on EVDR.VaultEntryID = AP.ARchivePointID

inner join enterprisevaultdirectory.dbo.archive EVDA on EVDA.RootIdentity = EVDR.RootIdentity

where modifieddate < 'date goes here'

Greg

[plaudone1 · Accepted solution]

Hi GTK,

This should work for you.

SELECT
EVDA.Archivename,
round(cast(AP.ArchivedItemsSize as float)/1024,2) as 'size of archive in MB',
Ap.ArchivedItems as 'no of archived items',
AP.modifieddate,
eme.MbxAlias

FROM archivepoint AP

Inner Join enterprisevaultdirectory.dbo.root EVDR on EVDR.VaultEntryID = AP.ARchivePointID

Inner Join enterprisevaultdirectory.dbo.archive EVDA on EVDA.RootIdentity = EVDR.RootIdentity
JOIN EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME on EME.DefaultVaultId = AP.ArchivePointId

WHERE modifieddate < '2016-08-24'

[GTK]

brilliant, works as expected

thanks a lot!