Total stats per archive
SELECT COUNT(t3.MSGID) AS ItemsCount, SUM(t3.MSGSIZE) as ItemsSize, t0.USERID, t0.USERNAME, t0.OBJDISTNAME, t0.EASSTATUS
FROM USERS AS t0
INNER JOIN REFER AS t1 ON(t0.USERID) = t1.USERID
INNER JOIN FOLDER AS t2 ON t1.FOLDERID = t2.FOLDERID
INNER JOIN PROFILE AS t3 ON t1.MSGID = t3.MSGID
WHERE(t1.MSGID >= 0) AND(t1.FOLDERID >= 0)
-- enable condition below in case you want to get stats for enabled/disabled archives
-- 0 = enabled
-- 1 = disabled
--and t0.EASSTATUS = 0
GROUP BY t0.USERID, t0.USERNAME, t0.OBJDISTNAME, t0.EASSTATUS
Result:
Archive with statistics
-- query from code used to get archive with statistics
SELECT t5.ROW_NUMBER, t5.USERID
AS UserId, t5.USERNAME
AS UserName, t5.OBJDISTNAME
AS ObjDistName, t5.ItemsCount
AS ItemsCount, t5.ItemsSize
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY t4.USERID) AS ROW_NUMBER, t4.USERID, t4.USERNAME, t4.OBJDISTNAME, t4.ItemsCount, t4.ItemsSize
FROM(
SELECT COUNT(t3.MSGID) AS ItemsCount, SUM(t3.MSGSIZE) as ItemsSize, t0.USERID, t0.USERNAME, t0.OBJDISTNAME
FROM USERS AS t0
INNER JOIN REFER AS t1 ON(t0.USERID) = t1.USERID
INNER JOIN FOLDER AS t2 ON t1.FOLDERID = t2.FOLDERID
INNER JOIN PROFILE AS t3 ON t1.MSGID = t3.MSGID
WHERE(t1.MSGID >= 0) AND(t1.FOLDERID >= 0)
-- enable condition below in case you want to get stats for enabled/disabled archives
-- 0 = enabled
-- 1 = disabled
--and t0.EASSTATUS = 0
GROUP BY t0.USERID, t0.USERNAME, t0.OBJDISTNAME ) AS t4) AS t5
Result:
Sizing stats per year
-- sizing stats per year
SELECT COUNT(t3.MSGID) AS ItemsCount, ((SUM(t3.MSGSIZE) / 1024) / 1024) as ItemsSizeMB, YEAR(t3.MSGDATE) as msgYear
FROM USERS AS t0
INNER JOIN REFER AS t1 ON(t0.USERID) = t1.USERID
INNER JOIN FOLDER AS t2 ON t1.FOLDERID = t2.FOLDERID
INNER JOIN PROFILE AS t3 ON t1.MSGID = t3.MSGID
WHERE(t1.MSGID >= 0) AND(t1.FOLDERID >= 0)
-- enable condition below in case you want to get stats for enabled/disabled archives
-- 0 = enabled
-- 1 = disabled
--and t0.EASSTATUS = 0
GROUP BY YEAR(t3.MSGDATE)
order by YEAR(t3.MSGDATE) desc
Result:
SourceOne
The following queries can be used for SourceOne. All query output units are in MB.
|
|
NOTE: Query outputs utilize compressed values to determine licensing. |
