03-02-2010 08:53 AM
Hello all,
Does any one have SQL parameters or codes for additional columns to existing SQL custom query/report to list Views or backup server?
Help is much appreciated.
Thanks.
03-02-2010 09:13 AM
for adding media server use the following:
select
substring(backupfinaljobstarttime,0,10) as 'Day',
count(distinct clientobject.objectprimarydisplayname) as '# of clients',
mediaobject.objectprimarydisplayname as 'Media Server',
count(distinct backupfinaljobid) as '# of Jobs',
(sum(backupsubjobsize))/1024/1024 as 'Size(GB)',
(AVG((CASE backupFinalJobStatus WHEN 2 THEN 0 ELSE 1 END))*100) AS 'Percentage'
from
object as clientobject,
obejct as mediaobject
backupfinaljob,
backupsubjob
where
backupfinaljobid=backupsubjobbackupfinaljobid and
backupfinaljobclientobjectid=clientobject.objectid and
backupfinaljobmediaserverobjectid=mediaobject.objectid and
backupfinaljobstarttime>=(now()-30) and
(backupfinaljobtype='0' or backupfinaljobtype='2')
group by
substring(backupfinaljobstarttime,0,10),
mediaobject.objectprimarydisplayname
To add a view in there use the following:
select
substring(backupfinaljobstarttime,0,10) as 'Day',
count(distinct clientobject.objectprimarydisplayname) as '# of clients',
mediaobject.objectprimarydisplayname as 'Media Server',
treename as 'View',
count(distinct backupfinaljobid) as '# of Jobs',
(sum(backupsubjobsize))/1024/1024 as 'Size(GB)',
(AVG((CASE backupFinalJobStatus WHEN 2 THEN 0 ELSE 1 END))*100) AS 'Percentage'
from
object as clientobject,
obejct as mediaobject
backupfinaljob,
backupsubjob,
tree,
node
where
nodeObjectID=clientObject.objectID and
nodeTreeID=TreeID and
backupfinaljobid=backupsubjobbackupfinaljobid and
backupfinaljobclientobjectid=clientobject.objectid and
backupfinaljobmediaserverobjectid=mediaobject.objectid and
backupfinaljobstarttime>=(now()-30) and
(backupfinaljobtype='0' or backupfinaljobtype='2')
group by
substring(backupfinaljobstarttime,0,10),03-08-2010 09:22 PM
05-20-2010 01:03 AM