Monitoring Oracle Tablespaces

General
,
4

Hi!
Im modifying the configuration file by myself in the server that i want to monitor. The files “60_mk_oracle.ps1”, “check_mk.ini”, “mk_oracle.cfg” and “Oracle.ManagedDataAccess.dll” are in “C:\ProgramData\checkmk\agent\plugins”.

60_mk_oracle.ps1

Parámetros de conexión a Oracle

$oracleUser = “USER”
$oraclePassword = “P@SSW0RD”
$oracleHost = “HOSTNAME”
$oracleSID = “ORACLESID”
$oraclePort = “1521”

Cadena de conexión sin privilegios de DBA

$connectionString = “User Id=$oracleUser;Password=$oraclePassword;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$oracleHost)(PORT=$oraclePort)))(CONNECT_DATA=(SERVICE_NAME=$oracleSID)))”

Conexión a Oracle

try {
Write-Host “Loading Oracle DLL…”
Add-Type -Path “C:\ProgramData\checkmk\agent\plugins\Oracle.ManagedDataAccess.dll”
Write-Host “DLL loaded successfully”

Write-Host "Creating Oracle connection..."
$oracleConnection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
Write-Host "Opening Oracle connection..."
$oracleConnection.Open()
Write-Host "Connection opened successfully"

# Consulta SQL mejorada para evitar valores negativos y mejorar precisión
$query = @"
SELECT t.tablespace_name, 
       ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS allocated_mb, 
       ROUND(SUM(df.bytes - NVL(f.free_bytes, 0)) / 1024 / 1024, 2) AS used_mb,
       CASE 
           WHEN SUM(df.bytes) = 0 THEN 0
           ELSE ROUND((SUM(df.bytes - NVL(f.free_bytes, 0)) / SUM(df.bytes)) * 100, 2) 
       END AS used_percent
FROM dba_tablespaces t
JOIN dba_data_files df ON t.tablespace_name = df.tablespace_name
LEFT JOIN (
    SELECT tablespace_name, SUM(bytes) AS free_bytes 
    FROM dba_free_space 
    GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name
HAVING SUM(df.bytes - NVL(f.free_bytes, 0)) >= 0

"@

Write-Host "Executing query..."
$command = $oracleConnection.CreateCommand()
$command.CommandText = $query
$reader = $command.ExecuteReader()
Write-Host "Query executed successfully"

# Mostrar el encabezado en el formato esperado por Checkmk
Write-Host "<<<oracle_tablespaces>>>"

# Leer los resultados
while ($reader.Read()) {
    $tablespace = $reader["tablespace_name"]
    $allocatedMb = $reader["allocated_mb"]
    $usedMb = $reader["used_mb"]
    $usedPercent = $reader["used_percent"]
    
    # Mostrar la salida en el formato esperado por Checkmk
    Write-Host "$tablespace|$allocatedMb|$usedMb|$usedPercent"
}

# Cerrar la conexión
$oracleConnection.Close()
Write-Host "Connection closed successfully"

} catch {
Write-Host “Error: $($_.Exception.Message)”
}

check_mk.ini

[plugins]
enabled = yes
execution =
- pattern: C:\ProgramData\checkmk\agent\plugins\60_mk_oracle.ps1
async: yes
group: Users
run: true

mk_oracle.cfg

oracleUser=USER
oraclePassword=P@SSW0RD
oracleHost=HOSTNAME
oracleSID=ORACLESID
oraclePort=1521