12-14-2020 10:14 PM
is there any specific database table, to get the total image file sizes in OnBase using sql server. i create a report, where i have to give the size of the image files for every month.
12-15-2020 07:52 AM
download the Database Reporting Guide- you can't miss
12-16-2020 06:43 AM
Hi Naveen, hsi.itemdatapage has the filesize column. I use the following to determine if there are any files with 0 as the filesize but you can adjust this to pull just the sizes without the where statement. Hopefully this is helpful to you!
SELECT
hsi.doctype.itemtypename AS [Document Type],
hsi.itemdata.itemname AS [Document Auto-Name],
hsi.itemtypegroup.itemtypegroupname AS [Document Type Group],
hsi.itemdatapage.filesize AS [File Size],
--COUNT(hsi.itemdatapage.filesize) AS [File Size],
hsi.itemdata.itemnum AS [Document Handle],
hsi.itemdatapage.physicalpagenum
FROM
hsi.itemdata INNER JOIN
hsi.doctype ON hsi.itemdata.itemtypenum = hsi.doctype.itemtypenum INNER JOIN
hsi.itemtypegroup ON hsi.doctype.itemtypegroupnum = hsi.itemtypegroup.itemtypegroupnum INNER JOIN
hsi.itemdatapage ON hsi.itemdata.itemnum = hsi.itemdatapage.itemnum
WHERE
(hsi.itemdatapage.filesize = 0)
12-16-2020 06:56 AM
Curious, after finding files with a zero size are you then purging any files of zero size for maintenance?
12-21-2020 12:52 PM
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT dg.diskgroupname AS 'Disk Group',
COUNT(idp.itemnum) AS '# Documents',
COUNT (idp.itemnum) AS '# Pages',
CAST (SUM(idp.filesize/1024/1024/1024) AS BIGINT) AS 'Space Used (GB)'
FROM hsi.itemdata i
INNER JOIN hsi.itemdatapage idp ON i.itemnum = idp.itemnum
INNER JOIN hsi.diskgroup dg ON idp.diskgroupnum = dg.diskgroupnum
WHERE idp.physicalpagenum = 0
AND i.datestored BETWEEN '2000-04-01' and '2021-04-30'--Change the date range.
GROUP BY dg.diskgroupname
ORDER BY dg.diskgroupname
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.