= CAST(DATEADD(DAY, -3, GETDATE()) AS date)
AND start_date < CAST(GETDATE() AS date)
AND policy LIKE '%Infra%'
AND schedule_type = 'full'
GROUP BY master
),
StartPoints AS
(
SELECT
j.master,
MIN(j.job) AS start_job
FROM dbo.NetBackupJobs j
INNER JOIN LatestFullDay d
ON d.master = j.master
AND CAST(j.start_date AS date) = d.full_day
WHERE
j.policy LIKE '%Infra%'
AND j.schedule_type = 'full'
GROUP BY j.master
)
";
/* ============================================================================
1. Résumé par master
========================================================================== */
$summarySql = $startPointCte . ",
Jobs AS
(
SELECT j.*
FROM dbo.NetBackupJobs j
INNER JOIN StartPoints s
ON s.master = j.master
AND j.job >= s.start_job
),
LargeIncrementals AS
(
SELECT
j.master,
COUNT(DISTINCT j.client) AS large_incremental_clients
FROM dbo.NetBackupJobs j
INNER JOIN StartPoints s
ON s.master = j.master
AND j.job >= s.start_job
WHERE
j.schedule_type = 'incremental'
AND j.status = 0
AND j.policy NOT LIKE '%Hyper%'
AND j.policy NOT LIKE '%standard%'
AND j.policy NOT LIKE '%WE%'
AND j.size_kb > 73400320
GROUP BY j.master
)
SELECT
m.master,
s.start_job,
COUNT(j.job) AS total_jobs,
COALESCE(SUM(CASE WHEN j.status <= 1 THEN 1 ELSE 0 END), 0) AS jobs_ok,
COALESCE(SUM(CASE WHEN j.status > 1 THEN 1 ELSE 0 END), 0) AS jobs_error,
COALESCE(SUM(
CASE WHEN j.schedule_type IN ('full','incremental') THEN 1 ELSE 0 END
), 0) AS scheduled_jobs,
COALESCE(SUM(
CASE WHEN j.schedule_type IN ('full','incremental')
AND j.status > 1 THEN 1 ELSE 0 END
), 0) AS scheduled_errors,
COALESCE(SUM(
CASE WHEN j.schedule_type = 'user backup' THEN 1 ELSE 0 END
), 0) AS user_jobs,
COALESCE(SUM(
CASE WHEN j.schedule_type = 'user backup'
AND j.status > 1 THEN 1 ELSE 0 END
), 0) AS user_errors,
COALESCE(li.large_incremental_clients, 0) AS large_incremental_clients
FROM Masters m
LEFT JOIN StartPoints s
ON s.master = m.master
LEFT JOIN Jobs j
ON j.master = m.master
LEFT JOIN LargeIncrementals li
ON li.master = m.master
GROUP BY
m.master,
s.start_job,
li.large_incremental_clients
ORDER BY m.master
";
$summary = Invoke_Infra($summarySql);
if (!is_array($summary)) $summary = [];
/* ============================================================================
2. Jobs en erreur + description NBRC
========================================================================== */
$errorSql = "
WITH LatestFullDay AS
(
SELECT
master,
MAX(CAST(start_date AS date)) AS full_day
FROM dbo.NetBackupJobs
WHERE
start_date >= CAST(DATEADD(DAY, -3, GETDATE()) AS date)
AND start_date < CAST(GETDATE() AS date)
AND policy LIKE '%Infra%'
AND schedule_type = 'full'
GROUP BY master
),
StartPoints AS
(
SELECT
j.master,
MIN(j.job) AS start_job
FROM dbo.NetBackupJobs j
INNER JOIN LatestFullDay d
ON d.master = j.master
AND CAST(j.start_date AS date) = d.full_day
WHERE
j.policy LIKE '%Infra%'
AND j.schedule_type = 'full'
GROUP BY j.master
)
SELECT
j.master,
j.job,
j.client,
j.policy,
j.schedule_type,
j.status,
r.RC_DESC AS status_desc,
j.start_date,
j.end_date,
j.size_kb
FROM dbo.NetBackupJobs j
INNER JOIN StartPoints s
ON s.master = j.master
AND j.job >= s.start_job
LEFT JOIN dbo.NBRC r
ON r.RC = j.status
WHERE
j.status > 1
ORDER BY
j.master,
CASE
WHEN j.schedule_type IN ('full','incremental') THEN 0
WHEN j.schedule_type = 'user backup' THEN 1
ELSE 2
END,
j.client,
j.job
";
$errors = Invoke_Infra($errorSql);
if (!is_array($errors)) $errors = [];
/* ============================================================================
3. Warnings : incrémentales > 70 GB
Règle métier exacte :
- incremental
- status = 0
- policy NOT LIKE Hyper / standard / WE
- size > 70 GB
- à partir du point de départ du master
On garde une ligne par master/client, correspondant au plus gros job du client.
========================================================================== */
$largeSql = "
WITH LatestFullDay AS
(
SELECT
master,
MAX(CAST(start_date AS date)) AS full_day
FROM dbo.NetBackupJobs
WHERE
start_date >= CAST(DATEADD(DAY, -3, GETDATE()) AS date)
AND start_date < CAST(GETDATE() AS date)
AND policy LIKE '%Infra%'
AND schedule_type = 'full'
GROUP BY master
),
StartPoints AS
(
SELECT
j.master,
MIN(j.job) AS start_job
FROM dbo.NetBackupJobs j
INNER JOIN LatestFullDay d
ON d.master = j.master
AND CAST(j.start_date AS date) = d.full_day
WHERE
j.policy LIKE '%Infra%'
AND j.schedule_type = 'full'
GROUP BY j.master
),
Candidates AS
(
SELECT
j.*,
ROW_NUMBER() OVER
(
PARTITION BY j.master, j.client
ORDER BY j.size_kb DESC, j.job DESC
) AS rn
FROM dbo.NetBackupJobs j
INNER JOIN StartPoints s
ON s.master = j.master
AND j.job >= s.start_job
WHERE
j.schedule_type = 'incremental'
AND j.status = 0
AND j.policy NOT LIKE '%Hyper%'
AND j.policy NOT LIKE '%standard%'
AND j.policy NOT LIKE '%WE%'
AND j.size_kb > 73400320
)
SELECT
master,
job,
client,
policy,
schedule_type,
status,
start_date,
end_date,
size_kb
FROM Candidates
WHERE rn = 1
ORDER BY master, size_kb DESC, client
";
$largeIncrementals = Invoke_Infra($largeSql);
if (!is_array($largeIncrementals)) $largeIncrementals = [];
/* ============================================================================
Préparation des données
========================================================================== */
$scheduledErrors = [];
$userErrors = [];
foreach ($errors as $row) {
$type = strtolower(trim((string)($row['schedule_type'] ?? '')));
if ($type === 'full' || $type === 'incremental') {
$scheduledErrors[] = $row;
} elseif ($type === 'user backup') {
$userErrors[] = $row;
}
}
$mastersOk = 0;
$mastersWarning = 0;
$mastersCritical = 0;
$totalJobs = 0;
$totalErrors = 0;
foreach ($summary as &$row) {
$total = db_int($row, 'total_jobs');
$ok = db_int($row, 'jobs_ok');
$err = db_int($row, 'jobs_error');
$large = db_int($row, 'large_incremental_clients');
$noStart = !isset($row['start_job']) || $row['start_job'] === null || $row['start_job'] === '';
$totalJobs += $total;
$totalErrors += $err;
if ($noStart || $total === 0 || ($total > 0 && $ok === 0)) {
$row['_state'] = 'critical';
$mastersCritical++;
if ($noStart) {
$row['_state_text'] = 'Aucun point de départ Infra trouvé';
} elseif ($total === 0) {
$row['_state_text'] = 'Aucune sauvegarde détectée';
} else {
$row['_state_text'] = 'Toutes les sauvegardes sont en erreur';
}
} elseif ($err > 0 || $large > 0) {
$row['_state'] = 'warning';
$mastersWarning++;
$parts = [];
if ($err > 0) $parts[] = $err . ' erreur(s)';
if ($large > 0) $parts[] = $large . ' incr. > 70 GB';
$row['_state_text'] = implode(' / ', $parts);
} else {
$row['_state'] = 'ok';
$row['_state_text'] = 'OK';
$mastersOk++;
}
}
unset($row);
$masterCount = count($summary);
$totalLarge = count($largeIncrementals);
/* ============================================================================
Helpers de rendu
========================================================================== */
function render_master_card($m) {
$state = $m['_state'] ?? 'ok';
$borderClass = [
'ok' => 'master-ok',
'warning' => 'master-warning',
'critical' => 'master-critical',
][$state];
$badgeClass = [
'ok' => 'text-bg-success',
'warning' => 'bg-warning text-dark',
'critical' => 'text-bg-danger',
][$state];
$largeClass = db_int($m, 'large_incremental_clients') > 0 ? 'text-warning' : 'text-success';
?>
| Master |
Client |
Policy |
Schedule |
Status |
Description |
Début |
Fin |
Taille |
Job |
|
|
|
|
|
|
|
|
|
|
Aucune incrémentale > 70 GB.
| Master |
Client |
Policy |
Taille |
Status |
Début |
Fin |
Job |
|
|
|
|
- OK |
|
|
|
NetBackup Daily —
masters —
OK -
Warning -
Critical
Résumé par master
Détails
0
);
render_accordion_block(
'user_errors',
'User Backup en erreur',
'bi-person-exclamation',
'bg-danger',
count($userErrors),
function () use ($userErrors) {
render_error_table('t_user_errors', $userErrors);
},
false
);
render_accordion_block(
'large_incrementals',
'Incrémentales > 70 GB',
'bi-exclamation-triangle',
'bg-warning text-dark',
count($largeIncrementals),
function () use ($largeIncrementals) {
render_warning_table('t_large_incrementals', $largeIncrementals);
},
count($largeIncrementals) > 0
);
?>