Opscenter Custom Report to exclude few error codes

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

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

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.

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

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

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.

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

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

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

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