SQL query to find items in mailbox older than one year?

[rajesh_velagapu]

SQL query to find items in mailbox older than one year?

[CareFreeXday]

Are you talking about the shortcut in mailbox? If so, you can not do this with SQL query. You need to use OL search.

[JesusWept3]

Really you should just do an index search,
But the query would be the following
 

SELECT A.ArchiveName "Archive Name",
    AF.FolderName "Folder Name",
    AF.FolderPath "Folder Path",
       S.IdTransaction "Transaction ID",
       S.IdDateTime "Sent/Received Date",
       S.ArchivedDate "Date Archived",
       S.ItemSize "Items Size"
      
FROM EnterpriseVaultDirectory.dbo.Archive A,
     EnterpriseVaultDirectory.dbo.Root R,
     EnterpriseVaultDirectory.dbo.Root R2,
     EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
     yourVaultStore.dbo.Saveset S,
     yourVaultStore.dbo.ArchivePoint AP,
     yourVaultStore.dbo.Vault V
    
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND S.VaultIdentity = V.VaultIdentity
  AND AP.ArchivePointId = R.VaultEntryId
  AND V.VaultId = R2.VaultEntryId
  AND R.RootIdentity = A.RootIdentity
  AND R2.RootIdentity = AF.RootIdentity
  AND S.IdDateTime <= '2007-01-01 00:00.000'
 

[rajesh_velagapu]

Thanks!!!

Query Error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'yourVaultStore.dbo.Saveset'

[rajesh_velagapu]

I need number of items for all the mailboxes older than one year. 

Query Error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'yourVaultStore.dbo.Saveset'

[SHI-CRO]

Unless your vault store is named "YourVaultStore" you need to change that part of the query to the name of your vault store.

[JesusWept3]

Rajesh :) I've given you like eleventy billion queries on this forum and they always do yourVaultStore.dbo... Anywho since you just want the counts an easier query would be USE YourVaultStore Select count(*) From Saveset Where idDateTime <= '2011-01-01 00:00.000' Swap "yourVaultStore" for the name of the vaultstore database

[rajesh_velagapu]

Sorry! I also need EV archive name and corresponding item count.

[JesusWept3 · Accepted solution]

Then edit the first query It's going to be something like Select a.archivename, count(s.idtransaction) From EnterpriseVaultDirectory.dbo.archive A, EnterpriseVaultDirectory.dbo.Root R, YourVaultStore.dbo.archivepoint AP, YourVaultStore.dbo.saveset S Where s.archivepointidentity = AP.archivepointidentity And AP.archivepointID = r.vaultentryid And r.rootidentity = a.rootidentity Group by archivename Order by archivename

[AndrewB]

eleventy billion. love it!