Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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:

Comments 14 CommentsJump to latest comment

Rob.Wilcox's picture

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?

JesusWept3's picture

give me a few minutes and i'll knock a script out for you
which version of EV?

ia01's picture

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

Rob.Wilcox's picture

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?]

ia01's picture

Exactly, need to know which vault store and partition an item in AuditTrial entry belongs to.

Many Thanks

Rob.Wilcox's picture

(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)

ia01's picture

 

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

JesusWept3's picture

its going to be a big query, almost done

JesusWept3's picture

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'

AttachmentSize
EV Audit Detailed Information Query.txt 4.43 KB
SOLUTION
JesusWept3's picture

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

ia01's picture

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

JesusWept3's picture

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