Forum Discussion

quebek's avatar
quebek
Moderator
12 years ago

OpsCenter client backup performance report

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?

Backup and Recovery
OpsCenter
Windows Server (2003-2008)
  • tom_sprouse's avatar
    tom_sprouse
    12 years ago

    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

     

     

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    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's avatar
    quebek
    Moderator
    12 years ago

    Thank you Tom!

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

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    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.