09-10-2013 12:42 AM
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?
Solved! Go to Solution.
09-10-2013 11:17 AM
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
09-10-2013 11:17 AM
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
09-11-2013 05:32 AM
Thank you Tom!
From where I can pull the information about the OpsCenter DB tables??
09-11-2013 05:57 AM
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.