# sql-backup-age.ps1 — newest FULL-backup age per database.
# Requires the SqlServer PowerShell module (Install-Module SqlServer -Scope CurrentUser)
# and Windows auth or a SQL login with VIEW SERVER STATE rights on msdb.

$Url        = "YOUR_URL/sqlbackup"
$ServerName = "localhost"   # or "HOST\INSTANCE"

$Query = @"
SELECT
  d.name AS DatabaseName,
  ISNULL(DATEDIFF(SECOND, MAX(b.backup_finish_date), GETUTCDATE()), 999999999) AS AgeSeconds
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
  ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4   -- skip master / model / msdb / tempdb
GROUP BY d.name
"@

try {
  $Rows = Invoke-Sqlcmd -ServerInstance $ServerName -Query $Query -ErrorAction Stop
} catch {
  $Body = (@{ hostname = $env:COMPUTERNAME; error = "$($_.Exception.Message)" }) | ConvertTo-Json -Compress
  Invoke-RestMethod -Uri $Url -Method Post -Body $Body -ContentType "application/json" -TimeoutSec 15 | Out-Null
  exit 1
}

$Stats    = @{}
$WorstAge = 0
foreach ($r in $Rows) {
  $age = [int]$r.AgeSeconds
  $Stats[$r.DatabaseName] = $age
  if ($age -gt $WorstAge) { $WorstAge = $age }
}

$Body = (@{
  hostname           = $env:COMPUTERNAME
  server             = $ServerName
  databases          = $Stats
  worst_age_seconds  = $WorstAge
  db_count           = $Rows.Count
}) | ConvertTo-Json -Compress -Depth 5

Invoke-RestMethod -Uri $Url -Method Post -Body $Body `
  -ContentType "application/json" -TimeoutSec 15 | Out-Null