Reporting by using SQL commands

You can run your own reports by querying the Tivoli Storage Manager server tables. In this section, we provide several examples of what you can do to report your VM backup activity.

Queries that use a SUMMARY_EXTENDED table can be run with Tivoli Storage Manager server version 6.3.3 or above.

8.2.1 Successful backup in a specified time frame

The first scenario that we describe is Successful Backup in Last 24 hours, which is a report to list all the VMs that were protected the last 24 hours, including backup type and number of bytes processed, as shown in the following example:

select SUB_ENTITY,ACTIVITY_TYPE,SUCCESSFUL,BYTES from SUMMARY_EXTENDED where START_TIME>current_timestamp - 24 hours and ENTITY like '%CLEM%DM01' and SUB_ENTITY IS NOT NULL AND SUCCESSFUL=’YES’

You can tune this query by using a script instead, as shown in Example 8-1.

Example 8-1 Script to report completed backups last $1 hours managed by datamover $2 define script REPORTOK_VM desc="VM backup stats for $1 hours , managed by datamover $2"

update script REPORTOK_VM "select SUB_ENTITY,ACTIVITY_TYPE,SUCCESSFUL,BYTES from SUMMARY_EXTENDED where END_TIME>current_timestamp - $1 hours and ENTITY like '$2' and SUB_ENTITY IS NOT NULL and SUCCESSFUL='YES'" line=10

tsm: VIOSSADEC>run REPORTOK_VM 90 CLEM_BROADSWORD_DM01

SLES11x64 - arryn Incremental Forever - Full YES 33559807580 ANR1462I RUN: Command script REPORTOK_VM completed successfully.

tsm: VIOSSADEC>run REPORTOK_VM 3 CLEM_TARGARYEN_DM01

tsmcetwin101 Incremental Forever - Incremental YES 315571611 tsmcetwin103 Incremental Forever - Incremental YES 324618405 tsmcetwin104 Incremental Forever - Incremental YES 862156452 tsmcetwin102 Incremental Forever - Incremental YES 812572325 ANR1462I RUN: Command script REPORTOK_VM completed successfully.

8.2.2 Unsuccessful backup in a specified time frame

In this scenario, we create a What Failed Last 24 hours report. The report shows which VM backups failed in the last 24 hours, including information about its datacenter node and the reason for the failure, as shown in the following example:

select nodename,message from actlog where DATE_TIME>current_timestamp - 24 hours and MSGNO=4174

8.2.3 Not backed up in a specified time frame

In this scenario, we create a Not Backed up Since report. By using this report, you can determine which virtual machines were not backed up since a specified time, including the last backup date, as shown in the following example:

select substr(FILESPACE_NAME,9,50) as "Virtual Machine",date(backup_end) from FILESPACES where filespace_name like '%VMFUL%' and backup_end<=current_timestamp - 24 hours and node_name like '%CLEM%' order by backup_end asc

You can tune this query by using a script instead, as shown in Example 8-2.

Example 8-2 Script to report missed backup since $1 days for $2 DC node define script NOTBACKEDUP_VM desc=”VM not backed up since $1”

update script NOTBACKEDUP_VM "select substr(FILESPACE_NAME,9,50),date(backup_end) from FILESPACES where filespace_name like '%VMFUL%' and

backup_end<=current_timestamp - $1 days and node_name='$2' order by backup_end asc" line=10 ANR1462I RUN: Command script NOTBACKEDUP_VM completed successfully.

8.2.4 Comparing successful full and successful incremental backups

It is often helpful to know how many incremental backups are being successfully run relative to the number of full backups that are being successfully run. Comparing these two numbers can provide insight into a backup trend for a VM, such as, errors that prevent incremental backups from being taken because of change block tracking (CBT) failures or other compatibility issues.

This report is designed for the Tivoli Storage Manager V6.4 incremental forever VM backup model and must be modified to report on the older “full plus incremental” model before version V6.4, as shown in the following example:

select x.SUB_ENTITY as "VM NAME", (select count(*) from summary_extended y where x.SUB_ENTITY=y.SUB_ENTITY and entity like '%DM%' and ACTIVITY_TYPE='Incremental Forever - Full' and SUCCESSFUL='YES' and START_TIME>CURRENT_DATE - INTERVAL '14' DAY) as "IFFULL", (select count(*) from summary_extended y where

x.SUB_ENTITY=y.SUB_ENTITY and entity like '%DM%' and ACTIVITY_TYPE='Incremental Forever - Incremental' and SUCCESSFUL='YES' and START_TIME>CURRENT_DATE - INTERVAL '14' DAY) as "IFINCR" from summary_extended x group by SUB_ENTITY ORDER BY IFFULL DESC, IFINCR DESC

You can tune this query by using a script instead, as shown in Example 8-3.

Example 8-3 Query comparing number of full versus incremental backups for each virtual machine tsm: WARDEN>select x.SUB_ENTITY as "VM NAME", (select count(*) from

summary_extended y where x.SUB_ENTITY=y.SUB_ENTITY and entity like '%DM%' and ACTIVITY_TYPE='Incremental Forever - Full' and SUCCESSFUL='YES' and

START_TIME>CURRENT_DATE - INTERVAL '14' DAY) as "IFFULL", (select count(*) from summary_extended y where x.SUB_ENTITY=y.SUB_ENTITY and entity like '%DM%' and ACTIVITY_TYPE='Incremental Forever - Incremental' and SUCCESSFUL='YES' and START_TIME>CURRENT_DATE - INTERVAL '14' DAY) as "IFINCR" from summary_extended x group by SUB_ENTITY ORDER BY IFFULL DESC, IFINCR DESC

VM NAME IFFULL IFINCR

8.2.5 Additional reporting information

For more information about the Tivoli Storage Manager Data Protection for VMware vCenter plug-in reporting, see this website:

https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/93193ba 0-be33-4733-9b03-9ab823a7475c/page/36548a5d-a9df-4e5a-ae3a-eada9f2f37ee/attachment /b8166859-548e-4504-b70f-989e865fa8f8/media/DPforVMware_Reporting640_V20130905.pdf