Ops Manager Daily Failed job report

[yobole]

Ops Manager Daily Failed job report

We have a master server based in Europe and a couple of media servers in different region. I need to generate a daily failed job report per media server rather than per master server. This will enable me to recharge adequately

Looking at Ops manager I can a daily failed job report but this does it for the entire system.. any ideas ?

Thanks in advance

[Will_Restore]

Start with Report Templates > Backup Reports > Job Browser Reports > Tabular Backup Report

Edit Report

Relative Time Frame > (1 day, 2 weeks, whatever)

Job > Job Status = Failed

Next

Media Server > Up arrow (7 times until it is on top)

(remove other unwanted columns if you like)

Run Report

(click on Media Server column heading to sort)

Save (provide a report name)

------

Then you can quickly access the report

Reports > My Reports > your report name

[yobole]

Thanks but does not give me what i want I wanted to set up a custom report to email just the Dailiy failed job but based  on a media server rather tham the master . Am a novice in SQL

[Will_Restore]

Please follow the above, step by step.  The report is sorted by media server & you don't need to know any SQL.

[M_henriksen]

Hi yobole.

Have you looked at this page: https://www-secure.symantec.com/connect/downloads/opscenter-common-custom-reports

On that page there is a report called "Last successful backup for failed jobs" I changed it a bit so check whether it can be used.

SELECT DISTINCT
A.mediaServerName as 'Media Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = CKP.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
            (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              BKP.Id = BKP.parentJobId AND
                              BKP.clientId = A.clientId AND
                              BKP.policyId = A.policyId AND
                              BKP.policyName = A.policyName AND
                              BKP.policyType = A.policyType AND
                             ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = A.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,mediaServerName
ORDER BY A.mediaServerName , A.clientName

Br Morten

[yobole]

Thanks Br Morten

this is really nice and excatly what I want however wold like to have two scripts

1) To use all the Media servers that contain "SE"

2)To use the Media servers that contain "UK"

Thanks in advance its really very helpfull

[M_henriksen · Accepted solution]

I have made it for "SE" then you can just replace that with "UK"

SELECT DISTINCT
A.mediaServerName as 'Media Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = CKP.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
            (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              BKP.Id = BKP.parentJobId AND
                              BKP.clientId = A.clientId AND
                              BKP.policyId = A.policyId AND
                              BKP.policyName = A.policyName AND
                              BKP.policyType = A.policyType AND
                             ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = A.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2) AND
A.mediaServerName like '%SE%'
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,mediaServerName
ORDER BY A.mediaServerName , A.clientName

[yobole]

Brillant works a treat .. Awesome

Thanks a lot