Does any one have SQL parameters or codes for adding columns to list the Views or backup server on existing SQL custom requport?

[DaizyBoy]

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.

[payners]

for adding media server use

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),
treename,
mediaobject.objectprimarydisplayname

[DaizyBoy]

Hi Payners,

Thank you very much for all your help.  Your high level of knowledge is very much appreciated.


Thanks again.

[turhan_sensoy]

Hi all,

I need a sqp querry that will create report such es "backups size, jobs count, clients count, percentage of backup success rate, failure rates and partial rate over period of time( like 30 days etc.)" . I try to run the SQL query above on OpsCenter but it give error says "Enter a valid query" it will be nice if some one can help me to create such a report on OpsCenter using sql query

Thanks for your interest

Regards,