Hi folks,
I'm building a custom report using SQL. The final output should look more or less like the “Backup Job Summary” report in Command Center. However, I'm running into issues with the Retention column.
I’ve figured out how to pull the standard retention using the following query:
SELECT
J.jobId,
AR.retentionDays
FROM JMJobDataStats J
INNER JOIN archGroup AG ON J.archGrpId = AG.id
INNER JOIN archAgingRule AR ON J.archGrpCopyId = AR.copyId
The problem is that this query doesn’t return jobs that have extended retention. I’m aware of the archAgingRuleExtended table and tried selecting from both tables and then taking the greater value — but this approach doesn’t seem to work.
I also tried applying the extended retention only when JMJobDataStats.retentionFlags > 0, but that didn’t help either 🙁
I’m sure I’m missing something obvious, but I can’t figure out what exactly. There must be some indication that a particular job is using extended retention, but I haven’t been able to find it.
Any pointers would be greatly appreciated!
