Need a SQL query to find out from which vault store and partition an item viewed in AutidTrail
Created: 01 Mar 2013 | Updated: 01 Mar 2013 | 14 comments
This issue has been solved. See solution.
Need a SQL query to find out from which vault store and partition an item viewed in AutidTrail
We have view and FSA Auditing Enabled. It has the folder info in the AuditTrail table. Can we find which Vault store and Vault store Partition this folder info or object that has been viewed from?
Audit Trail logs objectID something like 159326000000000~200911111157450000~0~A567BD863F0C4FD3B4BC5B8B1C636E3, can we reference that with a Vault store and Vault Store partition please?
Many Thanks in advance for your help.
Operating Systems:
Discussion Filed Under:
Comments 14 Comments • Jump to latest comment
I don't believe the savesetID as you have there, is stored in a single column in any of the tables in the vault store... so you'd have to break it down and find it that way ...
or...
doesn't the audit stuff tell you which ARCHIVE the item is in?
Many Thanks,
Rob
www.quadrotech-it.com - All your EV Tools
PS I hope that the post proves helpful.
AudiTrail give VaultID something like this
116044
give me a few minutes and i'll knock a script out for you
which version of EV?
Thanks JW
EV Version is 8.0.4
Basically we want to Delete some old Vault Store Partition. But we want to know whenther any items from those old vault stores has been accessed in past 1 month or so.
So we have auditing enabled for View and FSA
We can see from AuditTrail which items has been viewed in terms of UNC path of an item in the file server.
But don't know which Vault Store and Partition those items belongs to.
I know this can be dig down from SQL by joining multiple tables .... just don't know which DBs and tables to use to coreleate that information.
Many Thanks for your help
I'm confused over what you're trying to find out.
Do you want to know which folder on disk that item originates from ? ie the folder/path of the item, eg \\somefileserv\someshare?
Or do you want to know which Vault Store that item belongs to? [And if so.. why?]
Many Thanks,
Rob
www.quadrotech-it.com - All your EV Tools
PS I hope that the post proves helpful.
Exactly, need to know which vault store and partition an item in AuditTrial entry belongs to.
Many Thanks
(What I mean is that the item hasn't been viewed from a vault store or a vault store partition, it will have ben viewed from some UNC path)
Many Thanks,
Rob
www.quadrotech-it.com - All your EV Tools
PS I hope that the post proves helpful.
Thanks JW
EV Version is 8.0.4
Basically we want to Delete some old Vault Store Partition. But we want to know whenther any items from those old vault stores has been accessed in past 1 month or so.
So we have auditing enabled for View and FSA
We can see from AuditTrail which items has been viewed in terms of UNC path of an item in the file server
But don't know which Vault Store and Partition those items belongs to.
I know this can be dig down from SQL by joining multiple tables .... just don't know which DBs and tables to use to coreleate that information.
Many Thanks for your help
its going to be a big query, almost done
Attached is the really painfully long query, the results you get are as follows
To run, just paste in to a SQL Management Studio
The lines you need to edit are
SET @savesetID = '201303012162977~201301072255110000~Z~C0913673F800209463B0A06F7860A901'
Owaowwww
Thats a massive query!
Thanks very much for your time
actually, ping me later on cos you're going to get some weird inaccurate results, that saveset ID you posted above was EV2007 and below, the query really only works with EV8 items and above
so i will need to tweak it so that if it has ~0~ then its a 2007 saveset, if it has ~Z~ then its an Ev8 saveset
EV Was upgraded from EV2007 to EV8 at some Point! So I guess all the old savesets has ~0~ and all the new savesets after upgrade has ~Z~?
Many Thanks
Yeah so basically when EV8 came out, the format of the Saveset's and where they're stored got changed
In EV8+ you have this
[archivedDate]~[sentReceivedDate]~Z~[transactionID]
'201303012162977~201301072255110000~Z~C0913673F800209463B0A06F7860A901'
In EV2007 you have this
[idChecksumHigh][idChecksumLow]~[sentReceivedDate]~0~[transactionID]
159326000000000~200911111157450000~0~A567BD863F0C4FD3B4BC5B8B1C636E3
Ev8 items would be placed in
[partition path]\[archivedYear]\[archivedMonth-Day]\[firstCharofTID]\[nextThreeCharsOfTid]\[transactionID].dvs
E:\Vault Stores\Ptn1\2013\03-01\C\091\C0913673F800209463B0A06F7860A901.dvs
EV2007 items would be placed in
[partition path]\[sentYear]\[sentMonth]\[sentDay\[sentHour]\[savesetID].dvs
E:\Vault Stores\Ptn1\2009\11\11\11\159326000000000~200911111157450000~0.dvs
So what I need to do is just put something in so that if its a ~0~, then its a 2007 saveset, and to put it in the correct format, or if its Ev8+ then to use the other format
Would you like to reply?
Login or Register to post your comment.