03-10-2015 10:04 AM
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
03-10-2015 10:13 AM
Could you provide SQL queries, that OpsCenter is producing for your custom report in both cases?
03-11-2015 02:55 AM
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 )) ) ) )
03-11-2015 03:42 AM
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.
03-15-2015 06:31 AM
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.
03-15-2015 10:58 PM
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 != ''
03-15-2015 11:14 PM
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
03-16-2015 12:40 AM
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 != ''
03-16-2015 11:38 AM
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
03-16-2015 10:31 PM
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.
03-17-2015 01:31 PM
Thank you V, that work. I actually re-tweek to display size in kilobyte. Works just fine.