Custom Report Examples

Steps

For more information, see the following resources:

• Configuring Operations Center email reports
• Using the SELECT command

Space saved through compression and data deduplication

• Nodes with the best overall savings
• Best compressed nodes
• Worst compressed nodes
• Best deduplicated nodes
• Worst deduplicated nodes
• Storage pool space savings
• Storage pool space saving percentages

Client operations

• All node backups in the last 24 hours
• Summarized node backups in the last 24 hours
• Daily ingest

Use SELECT statements to view information about data deduplication and compression savings for a specific point in time. These queries provide the following information:

• Clients that are successfully deduplicating and compressing data.
• Storage pools with the highest amount of compressed or deduplicated data.

Reports the top ten space saving nodes by total savings percentage. 

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS  PROTECTED_GB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,  (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,  (CAST(FLOAT(SUM(s.comp_savings)+SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS  OVERALL_SAVINGS_GB,COALESCE((CAST(FLOAT(SUM(s.dedup_savings)+SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected  ))*100 AS DECIMAL(5,2))),0) AS OVERALL_SAVINGS_PCT from summary_extended s WHERE activity='BACKUP' or  activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY OVERALL_SAVINGS_PCT DESC FETCH FIRST 10 ROWS ONLY

Example output: Nodes with the best overall savings

Reports the top ten compressed nodes by compression percentage. 

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2)))   AS PROTECTED_GB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,   (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,   (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS   DEDUP_PCT,COALESCE((CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2))),0) AS COMP_PCT from summary_extended s WHERE activity='BACKUP' or activity='ARCHIVE' GROUP BY  S.ENTITY ORDER BY COMP_PCT DESC FETCH FIRST 10 ROWS ONLY

Example output: Best compressed nodes

Reports the ten nodes with the least compression savings by percentage.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2)))   AS PROTECTED_GB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,   (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,   (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT,  (CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2)))   AS COMP_PCT from summary_extended s WHERE activity='BACKUP' or   activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY COMP_PCT ASC FETCH FIRST 10 ROWS ONLY

Example output: Worst compressed nodes

Reports the top ten deduplicated nodes by deduplication percentage. 

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB,   (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,   (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,  COALESCE((CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))),0) AS DEDUP_PCT,  (CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2)))   AS COMP_PCT from summary_extended s WHERE DEDUP_SAVINGS<>0   and activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY DEDUP_PCT DESC FETCH FIRST 10 ROWS ONLY

Example output: Best deduplicated nodes

Reports the ten nodes with the least deduplication savings by percentage.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB,   (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,   (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,   (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT,  (CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2)))   AS COMP_PCT from summary_extended s WHERE DEDUP_SAVINGS<>0   and activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY DEDUP_PCT ASC FETCH FIRST 10 ROWS ONLY

Example output: Worst deduplicated nodes

Reports all storage pools that are on a single server. The table is sorted by total space saved.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  select STGPOOL_NAME, (CAST(SPACE_SAVED_MB as FLOAT)/1024) as TOTAL_SAVED_GB,   (CAST(DEDUP_SPACE_SAVED_MB as FLOAT)/1024) as DEDUP_SAVED_GB,   (CAST(COMP_SPACE_SAVED_MB as FLOAT)/1024) as COMP_SAVED_GB,   ((CAST(EST_CAPACITY_MB as FLOAT)/1024)*PCT_UTILIZED/100) as USED_SPACE_GB from stgpools   where STG_TYPE='DIRECTORY' or STG_TYPE='CLOUD' order by TOTAL_SAVED_GB DESC

Example output: Storage pool space savings

Reports the space that is saved for all storage pools for servers that are monitored by the Operations Center (TSMGUI_ALLSTG_GRID).

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  select name, SPACE_SAVED_PCT, SPACE_SAVED_MB, USED_SPACE, DEDUP_SAVED_MB, DEDUP_SAVED_PCT,   COMP_SAVED_MB, COMP_SAVED_PCT from TSMGUI_ALLSTG_GRID where STG_TYPE=101 or STG_TYPE=100   order by SPACE_SAVED_PCT DESC

Example output: Storage pool savings with percentages

Use SELECT statements to view information about nodes during a specific time period. These queries provide the following information:

• Clients that are not compressing or deduplicating data well.
• General overview of recent client activity.

Reports all backups or archived files for each node in the last 24 hours. The table is sorted by compression space saved.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT ENTITY as NODE_NAME, s.START_TIME, (CAST(FLOAT((s.bytes_protected))/1024/1024 AS DECIMAL(12,2))) AS  PROTECTED_MB, (CAST(FLOAT((s.bytes_written))/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_MB,  (CAST(FLOAT((s.dedup_savings))/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_MB,  (CAST(FLOAT((s.comp_savings))/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_MB,  (CAST(FLOAT((s.dedup_savings))/FLOAT((s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT from summary s WHERE  s.bytes_protected<>0 and (activity='BACKUP' or activity='ARCHIVE') and s.START_TIME>=(current_date - 1 days) order by  COMPSAVINGS_MB DESC

Example output: All node backups in the last 24 hours

Reports a summary of backups or archived files for each node in the last 24 hours. The table is sorted by compression space saved.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  select Entity as NODE_NAME, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_MB,  (CAST(FLOAT(SUM(s.bytes_written))/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_MB,   (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_MB,   (CAST(FLOAT(SUM(s.comp_savings))/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_MB,  (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT from summary s   WHERE s.bytes_protected<>0 AND (activity='BACKUP' OR activity='ARCHIVE') AND s.START_TIME>=(current_date - 1 days)   GROUP BY ENTITY order by COMPSAVINGS_MB DESC

Example output: Summarized node backups in the last 24 hours

Reports the total daily client ingest for the server. Includes total data protected as well as compression and deduplication savings. Sorted by most recent date.

Minimum IBM Spectrum Protect server version:

7.1.5.000

SQL Select statement

  SELECT DATE(s.START_TIME) AS Date,   (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB,  (CAST(FLOAT(SUM(s.bytes_written))/1024/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_GB,  (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB,  (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB,  (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT,   (CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2)))   AS COMP_PCT from summary s WHERE activity='BACKUP' or activity='ARCHIVE' GROUP BY DATE(S.START_TIME)   ORDER BY DATE DESC

Example output: Daily ingest