cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query help?

Scott__Hastings
Level 5
Partner Accredited Certified

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!!

Scott D Hastings Sr
1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

18 REPLIES 18

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 5
Partner Accredited Certified

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

 

 

Scott D Hastings Sr

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 5
Partner Accredited Certified

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'.

Scott D Hastings Sr

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

 

TS

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

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

Scott D Hastings Sr

JesusWept3
Level 6
Partner Accredited Certified

hmm
OK so try this and see what numbers return

SELECT C.CategoryName, 
       SC.SubCategoryName, 
       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
https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

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

 

CategoryName           auditcount     SubCategoryName
Admin Activity                    5                   OUTarget

Scott D Hastings Sr

JesusWept3
Level 6
Partner Accredited Certified

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!

https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

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

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

 

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

Scott D Hastings Sr

JesusWept3
Level 6
Partner Accredited Certified

OK so....

USE EnterpriseVaultAudit
SELECT *
FROM SubCategory

Then look for a result called Item and note the SubCategoryID

https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

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

SC.SubCategoryName,

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 D Hastings Sr

Scott__Hastings
Level 5
Partner Accredited Certified

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

 

Scott D Hastings Sr

JesusWept3
Level 6
Partner Accredited Certified

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

https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

TY !!

 

Is this just for enabled archives or all archives?

Scott D Hastings Sr

JesusWept3
Level 6
Partner Accredited Certified
All archives I can alter the query if you want only active mailboxes
https://www.linkedin.com/in/alex-allen-turl-07370146

Scott__Hastings
Level 5
Partner Accredited Certified

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

Scott D Hastings Sr