Video Screencast Help

SQL Query help?

Created: 04 Mar 2013 • Updated: 04 Mar 2013 | 18 comments
Scott _Hastings's picture
This issue has been solved. See solution.

I need to query SQL for all archives in an environment as the last the archive was accessed.

Is that in SQL?

Thanks for any help!!

Operating Systems:

Comments 18 CommentsJump to latest comment

TonySterling's picture

If you mean last time items were viewed you won't find that in SQL, only last time it had items added to it.

If you have auditing enabled for items accessed you might be able to do something with that.  Do you have that enabled?

Scott _Hastings's picture

Thanks Tony.. I have auditing with View, Delete, Restore with details enabled.

TonySterling's picture

Probably need to have JesusWept3 take a look but I think this might work.

Run against EnterpriseVaultAudit.

USE EnterpriseVaultAudit

SELECT MAX(at.AuditDate) as LastAccessed, v.VaultName as Archive
FROM AuditTrail at
JOIN EnterpriseVaultDirectory.dbo.ArchiveFolderView a ON a.ArchiveFolderIdentity = at.VaultId
JOIN EnterpriseVaultDirectory.dbo.VaultEntryView v ON v.VaultEntryId = a.VaultEntryId
WHERE at.CategoryID = 3
GROUP BY at.VaultId, v.VaultName   
ORDER BY at.VaultId

Scott _Hastings's picture

Tony, I'm on 9.0.3.

I get the following when I run the query.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'ArchiveFolderIdentity'.

TonySterling's picture

Hmm, I don't have an EV 9 lab handy.  Let me see what I can do.


JesusWept3's picture

This should do it I think

SELECT  DISTINCT(A.ArchiveName) "Archive", 
        MAX(AT.AuditDate) "Last Access Date"
FROM    EnterpriseVaultAudit.dbo.AuditTrail AT,
        EnterpriseVaultAudit.dbo.Vaults V,
        EnterpriseVaultDirectory.dbo.ArchiveFolderView AFV,
        EnterpriseVaultDirectory.dbo.Root R,
        EnterpriseVaultDirectory.dbo.Archive A
WHERE   AT.VaultId = V.VaultId
  AND   V.Vault = AFV.VaultEntryId
  AND   AFV.ContainerRootIdentity = R.RootIdentity
  AND   R.RootIdentity = A.RootIdentity
  AND   AT.SubCategoryID = 10
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
Scott _Hastings's picture

JW.. I ran it and got  no returns.. It took almost 10 minutes.

JesusWept3's picture

OK so try this and see what numbers return

SELECT C.CategoryName, 
       COUNT(AT.AuditID) AS auditcount
FROM   AuditTrail AT, 
       Categories C, 
       SubCategories SC
WHERE  AT.SubCategoryId = SC.SubCategoryId
  AND  AT.CategoryId = C.CategoryId
  AND  AT.SubCategoryId = 10
GROUP BY C.CategoryName, SC.SubCategoryName
Scott _Hastings's picture

This is all it returned JW.. I think my auditing database is screwed up.

CategoryName           auditcount     SubCategoryName
Admin Activity                    5                   OUTarget

JesusWept3's picture

hmmm ok so lets do this!
Do SELECT * FROM Subcategory and find the ID for "Item"
And then change AND   AT.SubCategoryID = 10 ... change 10 to be whatever Item is
I honestly thought it would be the same, but i guess not!

Scott _Hastings's picture

Sorry to have to ask JW.... but I'm misunderstanding

Select * From ???? where  ID  = '??'

I'm not familiar with the auditing database at all.

JesusWept3's picture

OK so....

USE EnterpriseVaultAudit
FROM SubCategory

Then look for a result called Item and note the SubCategoryID

Scott _Hastings's picture

did you mean Subcategories?  If so, here's what i got.

SubCategoryID   SubCategoryName  
1   ComputerEntry  
2   ExchangeMailboxEntry
3   Searches    
4   JournalingTarget  
5   VaultStoreEntry  
6   SiteEntry    
7   StorageServiceEntry
8   PartitionEntry  
9   FilterEntry  
10   OUTarget    
11   ExchangeServerEntry
12   ShoppingServiceEntry
13   PstDomain  
14   PstComputer  
15   AdminJobEntry  
16   PstFile    
17   PstMigrationHistory  
18   TaskControllerServiceEntry
19   IndexingServiceEntry
20   IndexRootPathEntry
21   RetentionCategoryEntry
22   AcceleratorEntry  
23   FileServerVolumeArchiveEntry
24   FileServerVolumeEntry
33   FileGroup  
34   FBActionMessages  
35   FBFileServerNodeProps
36   VaultStoreGroup  
38   view_VaultStoreGroup_FPCDB
39   Information  
40   Item    
41   VaultInterest  
42   ExtendedSetting  
43   FileScreen  
44   FileServerEntry  
45   FileServerFolderEntry
46   Access Denied  
47   TraceEntry  
48   MessageClassEntry  
49   Monitoring  
50   Organization  
25   DirectoryEntry  
26   PolicyEntry  
27   PhPolicySettingsEntry
28   DnaPolicySettingsEntry
29   RcPolicySettingsEntry
30   RuPolicyRulesEntry  
31   VolumePolicyEntry  
32   FolderPolicyEntry  

So now I'm running.

SELECT C.CategoryName,


COUNT(AT.AuditID) AS auditcount

FROM AuditTrail AT,

Categories C,

SubCategories SC

WHERE AT.SubCategoryId = SC.SubCategoryId

AND AT.CategoryId = C.CategoryId

AND AT.SubCategoryId = 40

GROUP BY C.CategoryName, SC.SubCategoryName

Scott _Hastings's picture

JW and Tony.... I really apprecaite the help.

Here are the results of that query. What now?

CategoryName SubCategoryName auditcount
Restore Item 76981
View Item 23769406
JesusWept3's picture

OK so go back to the big big query i posted above (the one that took 10 minutes) and then set SubCategoryId = 40

Scott _Hastings's picture

TY !!

Is this just for enabled archives or all archives?

JesusWept3's picture

All archives
I can alter the query if you want only active mailboxes

Scott _Hastings's picture

I need all, but what changes would I make for enabled?