06-20-2013 06:55 AM
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 ?
Solved! Go to Solution.
07-22-2013 08:29 AM
Was that query helpful? Or are you still looking for other items?
06-20-2013 07:41 AM
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?
06-20-2013 12:17 PM
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.
06-20-2013 01:23 PM
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.
06-21-2013 03:48 PM
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...
06-22-2013 07:09 AM
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....
06-22-2013 09:28 AM
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
06-27-2013 10:45 AM
Awesome.... I'll work on this Monday when I'm back in the office.
07-02-2013 01:16 PM
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....
07-05-2013 01:24 AM
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!!!!
07-05-2013 06:37 AM
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.
07-05-2013 07:05 AM
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.
07-05-2013 07:26 AM
This one is a bit better in that It breakes the success/failures down for each of the retention levels:
07-22-2013 08:29 AM
Was that query helpful? Or are you still looking for other items?
07-22-2013 08:49 AM
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.