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

SQL query

Created: 10 Jul 2013 • Updated: 29 Aug 2013 | 7 comments
GTK's picture
This issue has been solved. See solution.

hi

 

i am looking for a SQL query that will list out all our archived Public folders along with their compressed archive size and un-compressed size... is this possible?

 

Many Thanks

greg

Operating Systems:

Comments 7 CommentsJump to latest comment

JesusWept3's picture

This should work, apologies for typos or error as I'm on iPhone and not I front of an actual machine to test it

Select replace(cast(af.folderpath as varchar(max)), '?', '\ ') "folder",
Sum(s.itemsize)/1024 "Compressed Size (MB)",
Sum(sp.originalsize)/1024/1024 "uncompressed size (MB)"
FROM enterprisevaultdirectory.dbo.archive A,
enterprisevaultdirectory.dbo.Root R1,
Enterprisevaultdirectory.dbo.Root R2,
Enterprisevaultdirectory.dbo.archivefolder AF,
EVVSYourVaultStore.dbo.archivepoint AP,
EVVSYourVaultStore.dbo.vault V,
EVVSYourVaultStore.dbo.saveset S,
EVVSYourVaultStore.dbo.SavesetProperty SP
Where s.archivepointidentity = ap.archivepointidentity
AND s.vaultidentity = v.vaultidentity
AND s.savsetidentity = sp.savsetidentity
AND AP.archivepointid = R1.VaultEntryId
AND V.VaultID = R2.VaultEntryId
AND R1.rootidentity = A.Rootidentity
AND R2.rootidentitt = AF.rootidentity
AND A.ArchiveName = 'myPublicFolderArchive'
GROUP BY replace(cast(af.folderpath as varchar(max)), '?', '\ ')

GCSPT's picture

alternative if you just want the publicfolder type.

AttachmentSize
items per archive per size per archivetype.txt 1.2 KB

Symantec EV Support Engineer EMEA

GCSPT's picture

Apologies, needs VSDB to be on the Same server as VaultStoreDB which may not be the case on your system.

Symantec EV Support Engineer EMEA

GTK's picture

@JesusWept3

 

i get error -

Arithmetic overflow error converting expression to data type int.

JesusWept3's picture

Try changing

From:

Sum(s.itemsize)/1024 "Compressed Size (MB)",
Sum(sp.originalsize)/1024/1024 "uncompressed size (MB)"

To:

Sum(CAST(s.itemsize AS bigint))/1024 "Compressed Size (MB)",
Sum(CAST(sp.originalsize AS bigint))/1024/1024 "uncompressed size (MB)"

 

 

GTK's picture

@JesusWept3

 

i now get an output but th evast majority of data in "uncompressed size (MB) " column displays NULL even though the corresponding "compressed size (MB) " column does have a value . Any ideas why this is?

 

thanks

greg

JesusWept3's picture

How long have you had EV? The original size column i think was added in EV2007 (maybe EV7?)
its possible if its *really* old data then it just doesnt have a Original size?

Typically though I would have imagined it to be 0 if anything, null doesnt make much sense

I suppose you could do something like

 

SELECT A.ArchiveName "Archive Name",
       COUNT(S.SavesetIdentity) "Number of Savesets",
       SUM(CAST(S.ItemSize AS bigint))/1024 "Compressed Size (KB)"
FROM   EVVSYourVaultStore.dbo.Saveset S,
       EVVSYourVaultStore.dbo.ArchivePoint AP,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.ArchiveName = 'myPublicFolderArchive'
GROUP BY A.ArchiveName
SELECT A.ArchiveName "Archive Name",
       COUNT(SP.SavesetIdentity) "Number of Saveset Properties",
       SUM(CAST(SP.OriginalSize AS BigInt))/1024 "Original Size (KB)"
FROM   EVVSYourVaultStore.dbo.SavesetProperty SP,
       EVVSYourVaultStore.dbo.Saveset S
       EVVSYourVaultStore.dbo.ArchivePoint AP,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A
WHERE  SP.SavesetIdentity = S.SavesetIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.ArchiveName = 'myPublicFolderArchive'
GROUP BY A.ArchiveName

It could be something to do with the way that the size is cast in to a big int and divided by 1048576

SOLUTION