Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

OPS ANALYTICS CUSTOM REPORT (SQL QUERY)

Created: 20 Jun 2013 • Updated: 22 Jul 2013 | 14 comments
m.karampasis's picture
This issue has been solved. See solution.

We need help in order to create a custom report at OPS , attached you will find a template of the report as our Management team asked to be created.

We have bought Analytics License and all our efforts for the creation of the custom reports have failed.

Is there a way to add the retention period at a report ?

Operating Systems:
Discussion Filed Under:

Comments 14 CommentsJump to latest comment

MilesVScott's picture

A lot of this depends on how your backup jobs are configured. It looks like they are asking for different retention policies for different portions of the same client...

Are you using multiple policies to backup different parts of the same clients to differientiate retention levels?

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

m.karampasis's picture

You are right Scot there are more than one policy for the same client, although if we could some how export to the report the retention period for a number of policies it would be very helpful.

Another issue that we cannot export to the report is the Backup Attempts .

Both the Retention Periods & the Backup Attempts are data that OPS is aware of, because we can find them at the Policies tab but there is no existing report with these data. That's why we are thinking of the SQL queries.

MilesVScott's picture

I am actually not able to find retention in the OpsCenter database. I do see that there is a column under the domain_Schedule table for "retension" however for all of my policies this is NULL. I'm not sure if there is an issue with the collection because of the misnaming in the database. 

If someone can find the correct field in the database I could throw a query together, but without it more than half of what you are looking for would be missing.

I'm working on something that that field can be added to later, but the pivots are giving me some problems to get the failed count/success count's on the same line. I should have something soon.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

tom_sprouse's picture

Miles, 

I wish I could assist with the query, but I just do not have the time at the moment... however, I can offer the following... 

Look in - nb_RetentionLevel --- should break it down like... 

masterserverid, id, period, unit, label

This could be a nightmare for those who custom label their levels, and if they have multiple masters with different / custom levels...

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

MilesVScott's picture

Tom, That looks like a lookup table for the defined retention levels.

My issue with building the query is that I am unable to find how to associate those deffinitions back to the policies.

ie. The policies are lacking the Retention Level ID field... Or at least it's not populated in my system.

It looks like the logic was built into the domain_Schedule table, but again it looks like there was a typo in the column name which might be causing a proc to fail to fill the table with that data. I really can't say as I dont a good ERD for the database. But just my observations....

retension.JPG

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

tom_sprouse's picture

Miles,

Maybe this can help.......

Using the nb_retentionlevel for lookup / name, you should be able to join the following:

I found the retention level under...

domain_imagecopy.retentionLevel

joined with 

domain_imagecopy.imageID = domain_image.id 

then

domain_image.jobId = domain_job.id 

That will be one heck of a join statement... but it should work... 

--Tom

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

MilesVScott's picture

Awesome.... I'll work on this Monday when I'm back in the office.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

MilesVScott's picture

Here is what I have so far....

Still missing the retires, Final Success Ratio, and the pivot to make a single row for each, but this would at least get you some temporary data untill I can finish it up....

SELECT dj.clientName
        , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName AND j.statusCode = 0) AS 'Successful Jobs'
        , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName AND j.statusCode <> 0) AS 'Failed Jobs'
        , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName) AS 'Total Jobs'
        , '' AS 'Retries'
        , '' AS 'Final Success Rate'
        , rl.label AS 'RetentionLevel' 
    FROM domain_Job dj
INNER JOIN domain_image di
ON dj.id = di.jobId
INNER JOIN domain_ImageCopy ic
ON di.id = ic.imageId
INNER JOIN nb_RetentionLevel rl
ON ic.retentionLevel = rl.iD
WHERE DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 30
GROUP BY dj.clientName, rl.label
ORDER BY dj.clientName, rl.label

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

m.karampasis's picture

Thank you very much Miles that's great job!!!!

We are still trying to understand how we will add the "Retries" or "Attempts" to the report if you have any ideas please do not hesitate!!!!

MilesVScott's picture

I was actually going to ask you about that... Are you asking for statistics on when an opperator manually restarts a hung backup with checkpoint's enabled?

Otherwise Im not sure of any other retries, out side of regular scheduling. Unless you had your windows pretty open and short frequencies. I guess that would let the job kick off again similar to a retry.

Unfortunately, I don't have a good way to break either of those scnarios out right now. I'll dig a bit deeper into the data and see what I can find.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

m.karampasis's picture

Hello Miles we need the " Job Attempt Count " there are some default reports at the ops for that, although we cannot export the report per client or policy (as you can see attached).

What we need is that if there is a backup that wasn't successful at the 1st attempt then a way to know that at the one of the next attempts we had successfull backed up the data.

AttachmentSize
OpsCenter_FAILED.pdf 120.88 KB
OpsCenter_Atempts.pdf 121.49 KB
MilesVScott's picture

This one is a bit better in that It breakes the success/failures down for each of the retention levels:

SELECT dj.clientName
        , (SELECT COUNT(*) 
            FROM domain_Job domj
            INNER JOIN domain_image domi
            ON domj.id = domi.jobId
            INNER JOIN domain_ImageCopy domic
            ON domi.id = domic.imageId
            INNER JOIN nb_RetentionLevel nbrl
            ON domic.retentionLevel = nbrl.iD
            WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND domj.statusCode = 0 AND nbrl.id = rl.id) AS 'Successful Jobs'
        , (SELECT COUNT(*) 
            FROM domain_Job domj
            INNER JOIN domain_image domi
            ON domj.id = domi.jobId
            INNER JOIN domain_ImageCopy domic
            ON domi.id = domic.imageId
            INNER JOIN nb_RetentionLevel nbrl
            ON domic.retentionLevel = nbrl.iD 
            WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND domj.statusCode <> 0 AND nbrl.id = rl.id) AS 'Failed Jobs'
        , (SELECT COUNT(*) 
            FROM domain_Job domj
            INNER JOIN domain_image domi
            ON domj.id = domi.jobId
            INNER JOIN domain_ImageCopy domic
            ON domi.id = domic.imageId
            INNER JOIN nb_RetentionLevel nbrl
            ON domic.retentionLevel = nbrl.iD 
            WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND nbrl.id = rl.id) AS 'Total Jobs'
        , REPLACE(LEFT(100*ROUND(((CONVERT(NUMERIC(10,2),[Successful Jobs])/CONVERT(NUMERIC(10,2),[Total Jobs]))), 2), 3), '.', '') AS 'Final Success Rate'
        , rl.label AS 'RetentionLevel' 
    FROM domain_Job dj
INNER JOIN domain_image di
ON dj.id = di.jobId
INNER JOIN domain_ImageCopy ic
ON di.id = ic.imageId
INNER JOIN nb_RetentionLevel rl
ON ic.retentionLevel = rl.iD
WHERE UtcBigIntToNomTime(dj.endTime) > GETDATE() - 30
GROUP BY dj.clientName, rl.label, rl.id
ORDER BY dj.clientName, rl.label
 
 
 
 
DISCLAIMER: When Testing this for the last day's time frame, I found a few clients that showed more jobs on here than in the activity monitor. I have no idea why that is.... and I am still researching it.
 
EDIT: Added success rate

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

MilesVScott's picture

Was that query helpful? Or are you still looking for other items?

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

SOLUTION
m.karampasis's picture

Thank you very much MIles the only think that we cannot export from the report is the "Job Attempt Count" . I will mark your response as the solution.