cancel
Showing results for 
Search instead for 
Did you mean: 

Opscenter Custom Report to exclude few error codes

Sid1987
Level 6
Certified

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?

Client Name

Policy Name

Schedule Name

Job Start Time

Job End Time

Job Duration

Job File Count

Post Deduplication Size(MB)

Status Code

Job Status

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

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

15 REPLIES 15

Tape_Archived
Moderator
Moderator
   VIP   

Please check the attached screen shot. You can create a new report using the status code if you want show only specific codes in your report.

Use Control+Click to select individual codes from the list.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Try to replace "adjust_timestamp_dst" with "UTCBigIntToNomTime" for both of the lines dealing with the start/end times.

Sid1987
Level 6
Certified

Hi Riaan,

  It worked, However I don't think sql query would be the right solution to my scenario or if you know a way we can capture the data from this query every month without manual intervention like changing start date and end date before running the sql query. Please suggest.

 

Hi Tape_Archvied,

 I tried your method and it did work also, However when I created new report filtered the status codes it gave me more data (rows and pages) as compared to original report which is funny and told me there is something wrong, Now when I tried to edit the original report it didn't show me status code as one of the filtering parameters under Job section (attached image)

Now when I edited the new report I could see many parameters under job section. What am I doing wrong?

Thanks

Sid

 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

 

Hi,

You could use this  to determine the time frame instead of the BETWEEN clause

 

AND DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

or

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

 

In other words, for the past 1 month, or past 30 days

Sid1987
Level 6
Certified

Hi Riaan,

 I tried the query and it gave me around 65k rows, (image attached), below is the query I am using

select 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",UTCBigIntToNomTime(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",UTCBigIntToNomTime(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 ( ( (DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1) ) 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

Origial cutom report gives me around 35k rows(image attached)

Could you please try to see why my orginal custom report doesn't show status code as one of the filtering parameters? Because as I mentioned earlier when I am trying create a new custom report it does give me status code as filtering option under job parameter, However this new report gives me huge no of rows. I am confused what is going on and how did I created this original report I don't remember, but I am sure it was a custom report, but when I edit this it doesn't go to the begining of custom report where you decide what category of report will it be, but I am sure I used Backup/Recovery. Still I cant get the status code parameter under job section.

Thanks

Sid

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Sid

 

I don't use the customized reporting tools, I just construct my own queries. If you give a try you'll see its not that difficult. I started by running the standard reports and then grabbing the sql queries from the logs. Or not, by just clicking the show query button.

 

What I suggest you do is download the opscenter schema document, and then write your own basic query to start. After that you can expand on it.

 

The first I always do after grabbing the query (as shown above in your post) is to chop it up into readable pieces by making a new line when a new statement is added. It makes it a lot easier to figure out what you're trying to query.

 

Example (using some of your query above)

Select

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",

UTCBigIntToNomTime(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",

UTCBigIntToNomTime(domain_JobArchive.endTime ) as "domain_JobArchive.endTime",

domain_JobArchive.bytesWritten as "domain_JobArchive.bytesWritten",

 

Does that not look a whole lot better? Now you can see what your columns would be. I know this doesn't relate to the question of WTF is wrong with the query, but it will help you in understanding what the big blob text means. This will also help you look at it and remove stuff that is not relevant, and it will also allow you to see what you're filtering (looking) for. As shown below.

 

from domain_JobArchive , domain_MasterServer <<<<<<This shows the tables we're joining together and below it some conditions were looking for (not all of them but you can see its easier to understand)

where domain_MasterServer.id = domain_JobArchive.masterServerId

and domain_JobArchive.isValid = '1'

AND (DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

Anyways, just trying to give a you a start so you can discover the magic of SQL queries and maybe learn something new.

Sid1987
Level 6
Certified

Hi Riaan,

 Thanks for the detailed explanation, I am a CSc Btech :) , I am aware of DML, DCL, DDL.It's just that what was originally configured was a custom report. And what is difficult to understand is how come the query used gives different no of rows than the original report? Whereas in we have reduced it by filtering more, which gives me a feeling of something is wrong, that is what I am trying to figure out. The custom report was of 4 MB and now when I use query report it gives me a 14 MB report, do you get the feeling, how on earth is it possible when I am filtering error codes as well.

Let me see what I can do with the sql query, I have been using it but let me see, could you do a simple test in your environment as well if possible.

Thanks

Sid

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Sid,

I only have my test lab vm's so its difficult to just plugin a query like this. Try and start by running the most basic statement and limit it to the last day/hour. Then add more criteria and see if it behaves like it should.

I work by reverse engineering, I must be part Chinese :p

Sid1987
Level 6
Certified

Hi Riaan,

 Below is the query I have come up with,

select domain_JobArchive.clientId AS "domain_JobArchive.clientId",
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",
NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.ENDTime) AS "Job Duration",
domain_JobArchive.filesBackedUp AS "Job File Count",
domain_JobArchive.bytesWritten AS "Post Deduplication Size(MB)",
domain_JobArchive.statusCode AS "Status Code",
(CASE
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 "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
AND domain_JobArchive.scheduleId NOT IN (-2147483633 )
AND domain_JobArchive.statusCode NOT IN (174,288,196,50,811 )
AND domain_JobArchive.mASterServerId IN (61,22437 )
ORDER BY "domain_JobArchive.startTime" ASC

 

Now the issues with it is as below. it doesn't execute and gives me the error "OpsCenter-10881:Failed to execute specified SQL-- SQL Anywhere Error -143: Column 'domain_JobArchive.startTime' not found. "

Next if it gives an output I don't want "domain_JobArchive.clientId"

For Job Duration, how do I get hours, minutes and seconds below is the code I got from google but it gives error on Declare

DECLARE @TimeinSecond INT
SET @TimeinSecond = NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.ENDTime)
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2) AS "Job Duration"

Size is I guess is in Bytes, how do I convert it in MB with 2 decimal places?

And Job Status is coming as a number but I need it in characters as Successul(For error codes 0 and 1) And Failed(other error codes)

And lastly I would want everything Left Aligned.

How can I achive it and what am I doing wrong now?

 

Thanks

Sid

 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Sid,

First error solved by changing your last line to "ORDER BY "Job Start Time" ASC"

Second error solved by removing "domain_JobArchive.clientId AS "domain_JobArchive.clientId"," from the select first line

Size fixed but please check

Duration fixed

Alignment, no clue

Success/Fail bit, I'll look into that.

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 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 "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

 

Sid1987
Level 6
Certified

Hi Riaan,

 Almost through, I am sure the job status has something to do with table lookup_JobStatus. I am not sure how to manupulate the output in "Job Status" to have data respective to that of id in lookup_JobStatus? I am also trying at my end

Also is it possible to have comma separated numeric system as in cutom query?

Thanks

Sid

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Sid1987
Level 6
Certified

HI Riaan,

 Could you please check the below query, it does give me right output, However I would request you to verify it please.

select domain_MASterServer.friENDlyName AS "Master Server",
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(MB)",
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 Successful'
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode > 1 THEN 'Failed'
ELSE 'other'
END)  as "Job Status"
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
AND domain_JobArchive.scheduleId NOT IN (-2147483633 )
AND domain_JobArchive.statusCode NOT IN (6,11,12,13,21,29,150,636,174,288,196,50,54,811,40,42,43,41,46,90)
AND domain_JobArchive.mASterServerId IN (61,22437 )
ORDER BY "Job Start Time" ASC

Thanks

Sid

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Sid

I don't have a environment to run, so for me it reports nothing but i would expect that as I have very few jobs in my db.

I'm curious though, why do you exclude all these codes

AND domain_JobArchive.statusCode NOT IN (6,11,12,13,21,29,150,636,174,288,196,50,54,811,40,42,43,41,46,90)