cancel
Showing results for 
Search instead for 
Did you mean: 

I need a SQL query to get total count of items on hold and total size

JimmyNeutron
Level 6
Partner Accredited

Hi,

Does anyone happen to have a SQL query to collect the total number of items on legal hold and total size?

Per EV database is fine.

 

Regards,

Jaime

 

5 REPLIES 5

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello Jaime,

I can't get you an exact query, but I can get you some pointers:

https://www.veritas.com/support/en_US/article.100021469.html

https://www.veritas.com/support/en_US/article.100034248.html

https://www.veritas.com/support/en_US/article.100019406.html

You might be able to use these to add the size somehow.

 

Regards. Gertjan

ChrisLangevin
Level 6
Employee

How's this?

Simple:

 

--Returns number of items on legal hold and total size of items on legal hold in current Vault Store DB
SELECT COUNT(DISTINCT hs.SavesetIdentity) AS 'Number of held items', SUM(s.ItemSize) AS 'Size of held items (KB)' FROM HoldSaveset AS hs JOIN Saveset AS s ON s.SavesetIdentity = hs.SavesetIdentity

 

 

 

More detailed:

--Returns number of items on legal hold and total size of items on legal hold in current Vault Store DB,
--broken down by container for further detail
SELECT ISNULL(av.ArchiveName,'Total for VS') AS 'Container',
    COUNT(DISTINCT hs.SavesetIdentity) AS 'Number of held items',
    SUM(s.ItemSize) AS 'Size of held items (KB)'

FROM HoldSaveset AS hs
JOIN Saveset AS s ON s.SavesetIdentity = hs.SavesetIdentity
JOIN ArchivePoint AS ap ON ap.ArchivePointIdentity = s.ArchivePointIdentity
JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId

GROUP BY ROLLUP(av.ArchiveName)

 

 

--Chris

JimmyNeutron
Level 6
Partner Accredited

Will the results report on compressed or uncompressed?

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

ItemSize should be the compressed size as held in EV, I believe.

Yes, ItemSize is compressed. The OriginalSize is in the SavesetProperty table in bytes.  This would reflect in KB -

SUM(OriginalSize)/1024 AS OrignalSize_KB

Patrick