Opscenter Custom Report Issue

[Sid1987]

Hi Techiz,

 There is an issue while creating custom report in Opscenter(7.6.0.3), when I apply filter at the beginning at poly name contains "String", it doesn't return any data, However when I apply this filter with a string value which is "starting characters, e.g US" which is common in all policy names it works.

Any known issues?

Thanks

Sid

[VoropaevPavel]

Could you provide SQL queries, that OpsCenter is producing for your custom report in both cases?

[Sid1987]

Sure,

Here is the one on which I have Policy Name contains "NFS"(this string comes at the end of 4 policy names)

select TOP 20 START AT 1 domain_MasterServer.id as "domain_MasterServer.id",domain_Policy.name as "domain_Policy.name",domain_Client.id as "domain_Client.id",domain_Schedule.name as "domain_Schedule.name",adjust_timestamp_dst(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",adjust_timestamp_dst(domain_JobArchive.endTime ) as "domain_JobArchive.endTime",nb_JobFilesArchive.fileinfo as "nb_JobFilesArchive.fileinfo",domain_JobArchive.filesBackedUp as "domain_JobArchive.filesBackedUp",domain_JobArchive.bytesWritten as "domain_JobArchive.bytesWritten",(case when domain_JobArchive.state =106 then 3 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =0 then 0 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =1 then 1 when domain_JobArchive.state =3 and domain_JobArchive.statusCode>1 then 2 else -1 end) as "jobExitStatus",domain_JobArchive.statusCode as "domain_JobArchive.statusCode",domain_SkippedFileArchive.name as "domain_SkippedFileArchive.name" from domain_JobArchive , domain_Policy , domain_MasterServer , domain_Client , domain_Schedule , nb_JobFilesArchive , domain_SkippedFileArchive , domain_ScheduledJob where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_MasterServer.id = domain_Policy.masterServerId and domain_MasterServer.id = domain_Client.masterServerId and domain_MasterServer.id = domain_Policy.masterServerId AND domain_Policy.masterServerId = domain_Schedule.masterServerId and domain_Policy.name = domain_Schedule.policyName and domain_Policy.policyDomainName = domain_Schedule.policyDomainName and domain_Policy.versionNo = domain_Schedule.policyVersionNo and domain_MasterServer.id = domain_JobArchive.masterServerId AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_MasterServer.id = domain_SkippedFileArchive.masterServerId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId and domain_Client.masterServerId = domain_ScheduledJob.masterServerId and domain_Client.name = domain_ScheduledJob.clientName AND domain_Schedule.masterServerId = domain_ScheduledJob.masterServerId and domain_Schedule.policyName = domain_ScheduledJob.policyName and domain_Schedule.policyDomainName = domain_ScheduledJob.policyDomainName and domain_Schedule.policyVersionNo = domain_ScheduledJob.policyVersionNo and domain_Schedule.name = domain_ScheduledJob.scheduleName and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Client.masterServerId = domain_SkippedFileArchive.masterServerId and domain_Client.name = domain_SkippedFileArchive.clientName and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId and domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and ( ( (domain_JobArchive.endTime BETWEEN 136447591462790000 AND 136453603462790000) ) AND ( (UPPER(domain_Policy.name) LIKE UPPER('%NFS%')) ) AND ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61,22437 )) ) ) )

Here is the query for policy name containes "US" which comes in the beginning of all the policies in the environment

select TOP 20 START AT 1 domain_MasterServer.id as "domain_MasterServer.id",domain_Policy.name as "domain_Policy.name",domain_Client.id as "domain_Client.id",domain_Schedule.name as "domain_Schedule.name",adjust_timestamp_dst(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",adjust_timestamp_dst(domain_JobArchive.endTime ) as "domain_JobArchive.endTime",nb_JobFilesArchive.fileinfo as "nb_JobFilesArchive.fileinfo",domain_JobArchive.filesBackedUp as "domain_JobArchive.filesBackedUp",domain_JobArchive.bytesWritten as "domain_JobArchive.bytesWritten",(case when domain_JobArchive.state =106 then 3 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =0 then 0 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =1 then 1 when domain_JobArchive.state =3 and domain_JobArchive.statusCode>1 then 2 else -1 end) as "jobExitStatus",domain_JobArchive.statusCode as "domain_JobArchive.statusCode",domain_SkippedFileArchive.name as "domain_SkippedFileArchive.name" from domain_JobArchive , domain_Policy , domain_MasterServer , domain_Client , domain_Schedule , nb_JobFilesArchive , domain_SkippedFileArchive , domain_ScheduledJob where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_MasterServer.id = domain_Policy.masterServerId and domain_MasterServer.id = domain_Client.masterServerId and domain_MasterServer.id = domain_Policy.masterServerId AND domain_Policy.masterServerId = domain_Schedule.masterServerId and domain_Policy.name = domain_Schedule.policyName and domain_Policy.policyDomainName = domain_Schedule.policyDomainName and domain_Policy.versionNo = domain_Schedule.policyVersionNo and domain_MasterServer.id = domain_JobArchive.masterServerId AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_MasterServer.id = domain_SkippedFileArchive.masterServerId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId and domain_Client.masterServerId = domain_ScheduledJob.masterServerId and domain_Client.name = domain_ScheduledJob.clientName AND domain_Schedule.masterServerId = domain_ScheduledJob.masterServerId and domain_Schedule.policyName = domain_ScheduledJob.policyName and domain_Schedule.policyDomainName = domain_ScheduledJob.policyDomainName and domain_Schedule.policyVersionNo = domain_ScheduledJob.policyVersionNo and domain_Schedule.name = domain_ScheduledJob.scheduleName and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Client.masterServerId = domain_SkippedFileArchive.masterServerId and domain_Client.name = domain_SkippedFileArchive.clientName and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId and domain_JobArchive.masterServerId = domain_SkippedFileArchive.masterServerId and domain_JobArchive.clientName = domain_SkippedFileArchive.clientName and domain_JobArchive.id = domain_SkippedFileArchive.jobId AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and ( ( (domain_JobArchive.endTime BETWEEN 136447593072870000 AND 136453605072870000) ) AND ( (UPPER(domain_Policy.name) LIKE UPPER('%US%')) ) AND ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61,22437 )) ) ) )

[VoropaevPavel]

Hello Sid,

Could you clarify which of them doesn't work, both queries look very similar to me. And both didn't work for me even after I removed name filter. 

Also my opscenter is 7.6.0.4.

If you specify what exactly that you need, I could help you create a working query.

[Sid1987]

I am trying get daily report for certain policies which has NFS in their name, important part is i want selection list of the jobs in those policies with their completion status and other basic details like size, start time, end time, status, elapsed time. etc. So I tried with custom report.

[VoropaevPavel]

Hi Sid,

My guess that query doesn't work because of the number of joins. Or there is an error there that I don't see.

For day to day monitoring of the set of policies, I use following SQL.

SELECT
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.clientName as "Client Name",
domain_JobArchive.scheduleName as "Schedule Name",
adjust_timestamp(domain_ScheduledJob.scheduledTime,10800000) as "Job Scheduled Time",
UTCBigIntToNomTime(domain_JobArchive.startTime) as "Job Start Time",
UTCBigIntToNomTime(domain_JobArchive.endTime)      as "Job End Time",
DATEDIFF(minute,UTCBigIntToNomTime(domain_JobArchive.startTime),UTCBigIntToNomTime(domain_JobArchive.endTime)) as "Job Duration(Minutes)",
(CASE
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode=1 THEN 'Partial'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode>1 THEN 'Failed'
ELSE 'Failed' 
END)  as "Job Status",
domain_JobArchive.bytesWritten/1024/1024/1024 as "Job Size(MB)",
domain_JobArchive.statusCode as "Status Code"
FROM
domain_JobArchive,  lookup_JobState, domain_ScheduledJob
WHERE
domain_ScheduledJob.jobid  = domain_JobArchive.id AND
domain_JobArchive.policyName LIKE '%NFS%' AND
domain_JobArchive.state =lookup_JobState.id AND
DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=24 AND
domain_JobArchive.policyName != ''

[Sid1987]

Thanks,

 I also have a query, However I will try yours, it's missing File backed up in the job, which I can get no issues.

Thanks

Sid

[VoropaevPavel]

SELECT
domain_JobArchive.policyName as "Policy Name",
domain_JobArchive.clientName as "Client Name",
domain_JobArchive.scheduleName as "Schedule Name",
adjust_timestamp(domain_ScheduledJob.scheduledTime,10800000) as "Job Scheduled Time",
UTCBigIntToNomTime(domain_JobArchive.startTime) as "Job Start Time",
UTCBigIntToNomTime(domain_JobArchive.endTime)      as "Job End Time",
DATEDIFF(minute,UTCBigIntToNomTime(domain_JobArchive.startTime),UTCBigIntToNomTime(domain_JobArchive.endTime)) as "Job Duration(Minutes)",
(CASE
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode=1 THEN 'Partial'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode>1 THEN 'Failed'
ELSE 'Failed' 
END)  as "Job Status",
nb_JobFilesArchive.fileinfo as "Files",
CAST(domain_JobArchive.bytesWritten/1024/1024/1024 as NUMERIC(20,2)) as "Job Size(MB)",
domain_JobArchive.statusCode as "Status Code"

FROM domain_JobArchive,  lookup_JobState, domain_ScheduledJob, nb_JobFilesArchive 

WHERE domain_ScheduledJob.jobid  = domain_JobArchive.id 
AND domain_JobArchive.masterserverId = nb_JobFilesArchive.masterserverId 
AND domain_JobArchive.clientName = nb_JobFilesArchive.clientName 
AND domain_JobArchive.id = nb_JobFilesArchive.jobID
AND domain_JobArchive.policyName LIKE '%NFS%' 
AND domain_JobArchive.state =lookup_JobState.id AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=24 
AND domain_JobArchive.policyName != ''

[Dollypee]

Hi V, I tried to insert policy name that i want similar report on the last line. but return with and error.

Could you please advice which line to insert a policy name? Thank you

[VoropaevPavel]

Hi Dolly,

Replace NFS with part of policy name.

Note that it is case sensative.

>>AND domain_JobArchive.policyName LIKE '%NFS%'

In case if you want multiple policies

AND (domain_JobArchive.policyName LIKE '%NFS%' OR domain_JobArchive.policyName LIKE '%MYPOL%')

Query tested in opscenter 7.6.0.1 and 7.6.0.4, please share you error if you still getting it.

[Dollypee]

Thank you V, that work. I actually re-tweek to display size in kilobyte. Works just fine.