Opscenter Custom Report to exclude few error codes
Hi Team,
I already have an opscenter cutom report with below columns as part of the report. Now I need to exclude few error codes from this report, in the very first tab of the edit report section just let me filter once on status code parameter. Is it possible to achieve it?
Now when checked the sql query on this report from "Show Report Query" i gave me a query which I tried to run from run sql query section of report creation but it throws the error "OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -143: Column 'connection_level_dst_global_query_for_bigint' not found."
I tried so because i tought I can exclude error codes through below query which is the edited original query.
select TOP 100 START AT 1 domain_JobArchive.clientId as "domain_JobArchive.clientId",NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime) as "JobDuration",domain_JobArchive.filesBackedUp as "domain_JobArchive.filesBackedUp",domain_MasterServer.friendlyName as "domain_MasterServer.friendlyName",domain_JobArchive.policyName as "domain_JobArchive.policyName",domain_JobArchive.scheduleName as "domain_JobArchive.scheduleName",adjust_timestamp_dst(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",adjust_timestamp_dst(domain_JobArchive.endTime ) as "domain_JobArchive.endTime",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" from domain_JobArchive , domain_MasterServer where domain_MasterServer.id = domain_JobArchive.masterServerId and ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_JobArchive.endTime BETWEEN '136645617124190000' AND '136672437124190000') ) AND ( (domain_JobArchive.scheduleId NOT IN (-2147483633 )) ) AND ( (domain_JobArchive.statusCode NOT IN (174,288,196,50,811 )) ) AND ( ((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) NOT IN (-1 )) ) AND ( ( (domain_JobArchive.masterServerId IN (61,22437 )) ) ) ) ORDER BY "domain_JobArchive.startTime" ASC
Please suggest.
Thanks
Sid
select
domain_JobArchive.clientName AS 'Client Name',
domain_JobArchive.policyName AS "Policy Name",
domain_JobArchive.scheduleName AS "Schedule Name",
UTCBigIntToNomTime(domain_JobArchive.startTime ) AS "Job Start Time",
UTCBigIntToNomTime(domain_JobArchive.ENDTime ) AS "Job End Time",
convert(varchar(8), dateadd(SECOND,NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.ENDTime), '1970-01-01'), 108) AS "Job Duration",
domain_JobArchive.filesBackedUp AS "Job File Count",
cast((domain_JobArchive.bytesWritten/1048576) as numeric (20,2)) AS "Post Deduplication Size",
domain_JobArchive.statusCode AS "Status Code",
(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_MASterServer.friENDlyName AS "Master Server"
FROM domain_JobArchive , domain_MASterServer
WHERE domain_MASterServer.id = domain_JobArchive.mASterServerId
AND domain_JobArchive.isValid = '1'
AND DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
ORDER BY "Job Start Time" ASC
