OPS Centre Analytics SQL Report not working properly

[Woo]

Hello All,

The below script outputs a list of all failed backups over the last 7 days which have 'Monthly' in the schedule name. (I think I actually found this script on one of these posts).
This script works really well reporting on a NetBackup master server running 7.6.1.2 (Windows 2012), however the same script does not work so well on a server running 7.5.0.5 (Solaris 10).
On the 7.5.0.5 server, the script executes properly, however 'The report did not return any data' always appears, despite the NBU activity monitor showing failed 'Monthly' jobs.

Both OPS Centre Analytics versions are the same (7.6.1.2)
Both OPS Centre Servers are the same build. (Master servers are on different networks)
One master server is running NBU 7.6.1.2 (Windows 2012), the other is running 7.5.0.5 (Solaris 10)

I'm not %100 convinced that this issue is because of the NBU Master Server version difference.
Could this be a Windows/Solaris thing? Or could this be something with the SQL query?
Or could it be a conflict between OPS Centre 7.6.1.2 and the 7.5.0.5 master server?
Does anyone have any ideas?

Thanks

select TOP 5000 START AT 1
domain_JobArchive.clientname as "Client Name",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.scheduleName as "Schedule Name",
domain_JobArchive.statusCode as "Status Code" ,

adjust_timestamp(domain_JobArchive.startTime,36000000 ) as "Start Time" ,
adjust_timestamp(domain_JobArchive.endTime,36000000 ) as "End Time"

from domain_JobArchive , domain_MasterServer where domain_MasterServer.id = domain_JobArchive.masterServerId and ( (domain_JobArchive.isValid = '1') )

AND ( (domain_JobArchive.masterServerId IN (59 )) )

AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 7

AND ( (UPPER(domain_JobArchive.scheduleName) LIKE UPPER('%Monthly%')) )

AND ( (domain_JobArchive.type IN (0 )) )

AND ( ( (domain_JobArchive.masterServerId IN (59,1146,78085 )) ) )

AND domain_JobArchive.statusCode >1;

[RiaanBadenhorst · Accepted solution]

Hello,

The fact that it executes without error mean there is no syntax issue so you've probably defined a filter that is causing the issue. What I would do is to strip it down, run it, and then add more filters (AND clauses).

Also, this clause makes no sense as you don't ever use anything from the domain_masterserver table

from domain_JobArchive , domain_MasterServer

You can remove that from the query.

So step 1 is to run

select TOP 5000 START AT 1
domain_JobArchive.clientname as "Client Name",
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.scheduleName as "Schedule Name",
domain_JobArchive.statusCode as "Status Code" ,

adjust_timestamp(domain_JobArchive.startTime,36000000 ) as "Start Time" ,
adjust_timestamp(domain_JobArchive.endTime,36000000 ) as "End Time"

from domain_JobArchive

where domain_JobArchive.isValid in (1)

and domain_JobArchive.masterServerId IN (59)

If you get some results then move on to Step 2 and add

and DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 7

 Step 3 is to add

Next and

Step 4 is to add

Next and

And so forth

BTW the second last AND doesn't make sense. You've already told it to look for only jobs from master server 59. so you can't now again say, look for jobs from master server 59, 1146, 78085 etc [AND ( ( (domain_JobArchive.masterServerId IN (59,1146,78085 )) ) )]

Just remove that line from your query. I assume you're certain that 59 is the Master you're looking for.