Question

Add extended retention to the custom report.

  • 7 February 2024
  • 4 replies
  • 39 views

Badge +1

What line should I add in the custom report to show extended retention information instead of basic. Here is a part of script I’m running with basic retention string:

Return resuls in the given order
SELECT    CASE WHEN S.VMClientId > 0 THEN (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = S.VMClientId)
            ELSE CL.name END AS 'Client',
        IDA.name + CASE WHEN S.VMClientId > 0 THEN (' in ' + CL.name) ELSE '' END AS 'Agent',
        dbo.FixInstanceName(INS.name, A.appTypeId) AS 'Instance',
        BS.name AS 'Backupset',
        A.subclientName AS 'Subclient',
        C.PolicyName AS 'Storage Policy',
        C.CopyName AS 'Copy',
        C.StorageType AS 'Storage Type',
        CASE WHEN AGE.retentionDays < 0 THEN 'INFINITE' ELSE CAST(AGE.retentionDays AS VARCHAR(10))+ ' day(s);' + CAST(AGE.fullCycles AS VARCHAR(10)) +' Cycle(s)' END AS 'Retention',
        CAST(S.dataRead/1024.0/1024.0/1024.0 AS DECIMAL(10,2)) AS 'Application Size (GB)',
        CAST(S.dataWritten/1024.0/1024.0/1024.0 AS DECIMAL(10,2)) AS 'Data Written (GB)',
        ISNULL(CG.ClientGroups, 'N/A') AS 'Client Group'

 

This is output print screen:

 


4 replies

Userlevel 2
Badge +5

@Tomsky 

You’ll have to outer join with archAgingRuleExtended table. This table has row only if extended retention is configured so ISNULL check has to be there.

Badge +1

I can confirm that extender rules are configured, and that’s what I need to report. How and what string should I add or replace.

Userlevel 2
Badge +5

@Tomsky You can add this sql to your existing query

In select condition

ISNULL(AARE.retentionDays, -1) as retentionDaysExtended,
ISNULL(AARE.retentionRule, -1) as retentionRuleExtended
 

In join condition

LEFT OUTER JOIN archAgingRuleExtended AARE WITH (READUNCOMMITTED) ON AARE.copyId = archGroupCopy.id

 

Badge +1

Thank you Abhishek! I’ll try today and report.

Reply