Useful SQL Query (Item Details)

[Chris_Warren]

All, here is another large SQL Query I put together to help in identifying issues and details.  This one however runs against the Vault Store DB only.

Notes:

- As this cannot take into consideration every environment, please run these queries with caution as they MAY cause performance issues while being run

- This query is provided as-is and is in no way supported by Symantec Software.  All SQL queries provided within the public forums should be run with caution. 

-- Start Query

-- Prerequisite : Must be run on SQL instance with the Vault Store DB 

USE VaultStoreDBName

SELECT Distinct

     VSAV.IdTransaction

     ,(Cast (VSAV.idchecksumhigh as varchar(7)) + Cast(VSAV.idchecksumLow as varchar(8)) + '~' + Replace(Replace(Replace(Replace(CONVERT(VARCHAR(23), VSAV.iddatetime, 121),'-',''),' ',''),'.',''),':','') + '0~' + 'Z' + '~' + Replace(Cast(VSAV.idtransaction as varchar(36)),'-','') ) as SavesetID

     ,CASE WHEN VSAV.IdUniqueNo = '-1' THEN 'Post 8.x Item' ELSE 'Pre 8.x Item' END AS "Version Stored"  

     ,VSAV.ArchivePointId AS "ArchiveID"

     ,VSAV.VaultID AS "FolderID"

     ,VSAV.ArchivedDate AS "Archived Date"

     ,VSAV.IDDateTime AS "Item Creation Date"

     ,VSAV.ItemSize "Archived Items Size (KB)"

     ,VSP.OriginalSize/1024 "Original Item Size (KB)"

     ,SIS.SisParts AS "# Of SIS Parts"

     ,VSAV.RetentionCategoryIdentity

     ,CASE WHEN VSAV.collectionidentity is NOT null THEN CONVERT(VARCHAR(24), VSAV.CollectionIdentity) ELSE 'N/A' END AS CollectionIdentity 

     ,CASE WHEN Collection.collectionidentity is NOT null THEN Collection.RelativeFileName ELSE 'Not Collected' END AS CollectionName

     ,CASE WHEN SStore.StoreIdentifier is NOT null THEN SStore.StoreIdentifier ELSE 'Not Single Clip (Centera)' END AS SingleClipName      

     ,CASE WHEN VH.SavesetIdentity is null THEN 'Not on Legal Hold' ELSE 'On hold' END AS "OnHold_CA/DA"

     ,CASE WHEN JA.TransactionID is null THEN 'Not in JA Table' ELSE 'In JA Table' END AS SS_IN_JA

     ,CASE WHEN JS.ItemSeqNo is null THEN 'Not in JS Table' ELSE 'In JA Table' END AS SS_IN_JStub

     ,CASE WHEN WF.ArchiveTransactionID is null THEN 'Not in WF' ELSE 'In Watchfile' END AS SS_IN_WF

     ,CASE WHEN JD.ItemSeqNo is NOT null THEN CONVERT(VARCHAR(24), JD.DeletionDate) ELSE 'N/A' END AS JD_DeletionDate_UTC

     ,CASE WHEN JD.DeletionStatus = '0' THEN 'Not Deleted from Storage' WHEN JD.DeletionStatus = '1' THEN 'Deleted from Storage' ELSE 'N/A' END AS 'DeletionStatus'

     ,CASE WHEN JD.DeletionReason = '0' THEN 'None' WHEN JD.DeletionReason = '1' THEN 'By User' WHEN JD.DeletionReason = '2' THEN 'By Expiry' WHEN JD.DeletionReason = '3' THEN 'By System' WHEN JD.DeletionReason is NULL THEN 'N/A' ELSE 'Unknown' END AS DeletionReason

     ,CASE WHEN JD.IndexCommitted = '1' THEN 'Removed' WHEN JD.IndexCommitted = '0' THEN 'Not Removed' ELSE 'N/A' END AS 'Delete Removed From Index'

FROM

     View_Saveset_Archive_Vault VSAV

     LEFT OUTER JOIN JournalArchive JA on JA.TransactionID = VSAV.IDTransaction

     LEFT OUTER JOIN WatchFile WF on WF.ArchiveTransactionID = VSAV.IDTransaction

     Left Outer Join JournalDelete JD ON JD.ArchivePointIdentity = VSAV.ArchivePointIdentity AND jd.ItemSeqNo = VSAV.IndexSeqNo

     LEFT OUTER JOIN Collection on VSAV.CollectionIdentity = Collection.CollectionIdentity

     LEFT OUTER JOIN view_Holds VH on VH.SavesetIdentity = VSAV.SavesetIdentity

     LEFT OUTER JOIN SavesetStore SStore on SStore.SavesetIdentity = VSAV.SavesetIdentity

     Left Outer Join JournalStub JS ON JS.ArchivePointIdentity = VSAV.ArchivePointIdentity AND JS.ItemSeqNo = VSAV.IndexSeqNo

     JOIN View_Saveset_Properties VSP on VSAV.SavesetIdentity = VSP.SavesetIdentity

     JOIN View_Saveset_Properties_With_SisPartCount SIS on VSP.SavesetIdentity = SIS.SavesetIdentity 

 WHERE

--Caution - Depending on the number of items returned by query, this can be potentially a heavy query for SQL

--CAN USE 'OR' STATEMENT TO GIVE INDIVIDUAL TRANSACTIONS

--VSAV.IDTransaction = 'F103E86F-BEEA-E351-63A6-0D874497C441'

--or VSAV.IDTransaction = ''

--or VSAV.IDTransaction = ''

--CAN USE ARCHIVEID (ArchivePointID)

--VSAV.ArchivePointId = '1C2810911B5BF504EA59C7479A67C63331110000EVserver'

--CAN USE VAULTID (ArchiveFolderID)

--VSAV.VaultID = '1C0FFE33252D36F419FF4FAA359FEA7721110000EVserver'

--CAN USE RETENTIONCATEGORYIDENTITY ASSIGNED TO ITEM

--VSAV.RetentionCategoryIdentity = '1'

--CAN USE SAVESETID FROM JOURNALDELETE *IF PRESENT*

--JD.SavesetID = '201211274054837~201211272213490000~Z~E142338108B9206E5A3D0134D0458A01'

--Can use other attributes to limit the results, including any reference in JournalArchive (Ex. JA.TransactionID) or JournalDelete (Ex. JD.SAVESETID)

Information Gathered:

a. IdTransaction : This is a unique value referring to a specific item archived.

b. A logical construction (using post 8.0 saveset formatting) of the full SavesetID.

c. Whether or not the item was stored using pre-EV 8.0 or post-EV 8.0 storage.

d. ArchivePointID : This is the ArchiveID of the Archive. A unique identifier for the specific archive.

- This value can be located in the VAC. Right-click on the archive : Properties : Advanced.

e. VaultID : This is the unique identifier for the individual folder the item is in.

f. Archived Date of the item.

g. Item Creation Date, or Send/Received time.

h. Archive Item size in KB.

i. Original Item Size in KB.

j. Number of SIS parts associated with item.

k. Retention Category Identity

- This can then be used in a subsequent query against EnterpriseVaultDirectory.RetentionCategoryEntry for details of the retention category item is associated with.

l. If Item is collected (CAB or Centera Clip), what is the CollectionIdentity.

m. If collected, Path of the Collection file. If using Centera with Collections, the Clip ID.

n. --Centera : If using Centera without Collections, the Clip ID.

o. If item is on Legal Hold.

p. If item is in JournalArchive Table (JA)

q. If Item is in JournalStub Table (JS)

r. If item is in Watchfile Table (WF)

s. If item is in JournalDelete (JD) Table, the Deletion Date.

t. If in JD, Deletion Status (If item has been deleted from storage).

u. If in JD, Deletion Reason (How item was deleted).

v. If in JD, Index Committed (If deletion was committed to the index).

As referenced in the WHERE clause, this may be run using various criteria to gain a good consolidated look at the status of items in the Vault Store. Again, if anyone sees something wrong about it, let me know.

Enjoy!

[EV_Ajay]

Hi Chris,

Thank you for sharing this information. Which is very useful information.