03-04-2013 11:59 AM
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!!
Solved! Go to Solution.
03-04-2013 07:01 PM
OK so go back to the big big query i posted above (the one that took 10 minutes) and then set SubCategoryId = 40
03-04-2013 12:06 PM
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?
03-04-2013 12:35 PM
Thanks Tony.. I have auditing with View, Delete, Restore with details enabled.
03-04-2013 12:55 PM
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
03-04-2013 01:01 PM
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'.
03-04-2013 02:56 PM
Hmm, I don't have an EV 9 lab handy. Let me see what I can do.
TS
03-04-2013 04:04 PM
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
03-04-2013 04:22 PM
JW.. I ran it and got no returns.. It took almost 10 minutes.
03-04-2013 04:25 PM
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
03-04-2013 05:57 PM
This is all it returned JW.. I think my auditing database is screwed up.
CategoryName auditcount SubCategoryName
Admin Activity 5 OUTarget
03-04-2013 05:59 PM
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!
03-04-2013 06:15 PM
Sorry to have to ask JW.... but I'm misunderstanding
Select * From ???? where ID = '??'
I'm not familiar with the auditing database at all.
03-04-2013 06:39 PM
OK so....
USE EnterpriseVaultAudit
SELECT *
FROM SubCategory
Then look for a result called Item and note the SubCategoryID
03-04-2013 06:48 PM
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
03-04-2013 06:59 PM
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 |
03-04-2013 07:01 PM
OK so go back to the big big query i posted above (the one that took 10 minutes) and then set SubCategoryId = 40
03-04-2013 07:32 PM
TY !!
Is this just for enabled archives or all archives?
03-04-2013 08:02 PM
03-04-2013 08:04 PM
I need all, but what changes would I make for enabled?