cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter client backup performance report

quebek
Moderator
Moderator
   VIP    Certified

Hello

I am looking for a tabular report which will provide me information about given client name and:

backup date, backup type (full, cinc, dinc), backup duration (hh:mm:ss), backup size (kB), files backed, snapshot removal (how long it took) - for vmware based backups hh:mm:ss

I am running OCA in 7.5.0.5 version. Can any one shoot a SQL query for the above?

1 ACCEPTED SOLUTION

Accepted Solutions

tom_sprouse
Level 6
Employee Accredited Certified

Quebek,

Here is the SQL with 1 exception, the snapshot removal time - is not present.

As far as I know, That information is not captured by OpsCenter.

However, please test the following SQL and let me know what you think!

 

SELECT clientName AS "Client",
lookup_ScheduleType.name AS "Schedule Type",
utcbiginttonomtime(startTime) AS "Start Time",
utcbiginttonomtime(endTime) AS "End Time",
DATEDIFF(mi, utcbiginttonomtime(StartTime), utcbiginttonomtime(EndTime)) AS "Duration (Min)",
filesBackedUp AS "File Count",
(bytesWritten/1024) AS "Size (Kilobytes)"
FROM domain_job
JOIN lookup_ScheduleType
ON lookup_ScheduleType.id = domain_job.scheduleType
WHERE clientName LIKE '%clientname%'

You will need to modify the %clientname% in the last line - and provide a client name you wish to report on.

 

If you want to return all clients - then remove the WHERE line all together.

Please note: There is no time limitation on this statement and will return ALL historical information for each client. This could take a long time to execute.

If a time statement is needed, let me know, and I can attempt to add it.

If this resolves your issue, please mark this thread accordingly.

 

Thank you!

--Tom

 

 

View solution in original post

3 REPLIES 3

tom_sprouse
Level 6
Employee Accredited Certified

Quebek,

Here is the SQL with 1 exception, the snapshot removal time - is not present.

As far as I know, That information is not captured by OpsCenter.

However, please test the following SQL and let me know what you think!

 

SELECT clientName AS "Client",
lookup_ScheduleType.name AS "Schedule Type",
utcbiginttonomtime(startTime) AS "Start Time",
utcbiginttonomtime(endTime) AS "End Time",
DATEDIFF(mi, utcbiginttonomtime(StartTime), utcbiginttonomtime(EndTime)) AS "Duration (Min)",
filesBackedUp AS "File Count",
(bytesWritten/1024) AS "Size (Kilobytes)"
FROM domain_job
JOIN lookup_ScheduleType
ON lookup_ScheduleType.id = domain_job.scheduleType
WHERE clientName LIKE '%clientname%'

You will need to modify the %clientname% in the last line - and provide a client name you wish to report on.

 

If you want to return all clients - then remove the WHERE line all together.

Please note: There is no time limitation on this statement and will return ALL historical information for each client. This could take a long time to execute.

If a time statement is needed, let me know, and I can attempt to add it.

If this resolves your issue, please mark this thread accordingly.

 

Thank you!

--Tom

 

 

quebek
Moderator
Moderator
   VIP    Certified

Thank you Tom!

From where I can pull the information about the OpsCenter DB tables??

tom_sprouse
Level 6
Employee Accredited Certified

Quebek,

Using DBISQLC, you can dump the table names using the following:

SELECT * FROM SP_TABLES();

OUTPUT TO "C:\Tables.txt";

You cannot run this from the WebUI....

To use the dbisqlc application, please refer to the following:

With Windows Explorer,

Navigate to C:\Program Files\Symantec\OpsCenter\server\db\WIN64 

Launch the program "dbisqlc.exe" from this location. 

To connect to the database with dbisqlc, enter: 

User ID: DBA 
Password: SQL 

Click on Database Tab 
Uncheck "Stop database after last disconnect" 

Enter the Server name: OPSCENTER_hostname    

(example Server name: OPSCENTER_RPTSRV01 )

Click on OK

In the lower screen of dbisqlc labeled "Command", enter the following: 

SELECT * FROM SP_TABLES();

OUTPUT TO "C:\Tables.txt";

 

Confirm the tables.txt file now exists on the root of C: or whichever path you specify.

Exit dbisqlc.