Looking for custom OpsCenter Analytics SQL Script

I do not see an attachment . Could you please clarify what esactly you want .

i am looking to use a SQL query to create a report - does anyone know how i can obtain the ops center analytics schema information tyo get the table names that i can use in the query

any help would be appreciated

many thanks

Tracy

i ahve found the answer to my own question - the ops center schema can be found here:-

in opscenter server, under C:\Program Files\Symantec\OpsCenter\server\db there is a zip named db_sqls.zip. inside the zip, there is the folder "70" and inside this the file named schema.xml

Tracy

Hi Tracy,

You can find attached information about the database schema of OpsCenter. You can see in the pdf document a description of each field of tables, and you can find in the zip file  some visio documents explaining the relationships between all tables.

I attended to Symantec Vision 2001 at Barcelona and one of the technicians of Opscenter's lab session gave me this information.

I also want to do start doing customized reports in OpsCenter with SQL querys, but I still have had not time.

Hope this help you.

César.

Hi César - thank you very much for the attachments and additional info - that is very useful - when i get some decent queries written i will share them on this topic as they may be of use

kind regards

Tracy

Great! thank you for sharing your queries. I'm sure that  they will be very usefull to me.
 

kind regards,

César.

Hey Cesar...since you attended Vision did you take part in the MG216-L: OpsCenter and OpsCenter Analytics Hands On Lab ??? On page 18 it talks about two SQL Query that were used during the hand on lab...

Custom SQL Reporting 

OpsCenter Analytics offers the ability to use custom SQL scripting to pull reports. This is pretty advanced stuff and requires the customer to understand the SQL database structure, command calls and know how to make use of the Schema that we publish. Not for the faint hearted. While it is beyond the scope of this HOL to show you how to create a custom SQL report, we did want to show you what one looks like. If you are interested in doing this, follow these steps which should take just a few minutes. 

Most customers who run dedupe are asking about “dedupe savings”. Is it really worth it? While the canned OC reports can show this at a glance, it doesn‟t really drill down into the data to truly show a nice table breakdown of the savings. Check out these custom SQL queries that really show it off. 

1. Click on Reports 

2. Click on Create New Report 

3. Select Create a report using SQL Query 

4. Click Next 

5. On your desktop, locate a file named “OCA Custom SQL 1.txt and open it with notepad 

6. There is information about the script and what it will be doing to pull the information in the top part of the .TXT file. Read it. 

7. Highlight the text in the second paragraph starting at SELECT „Last 24 hours‟ all the way to the end 

8. Copy this information and paste it into the SQL Report box 

9. Click Next 

What you will see is a report showing the dedupe information from the last 24 hours, seven days and a total. It shows how much information was “Pre Dedupe” and the breaks it down to how much was actually backed up. This is a powerful report for a customer who wants to know how much storage space is being saved by dedupe. 

Now, lets say they want to see it per Policy. Check this out. 

1. Click Back to get back to the box you pasted the report into 

2. Open OCA Custom SQL 2.txt from the desktop 

attachment

This is exacly what I was looking for.