Transmission of (Database) Table Data to Prometheus

[Píer Bauer]

Hi there,

I'm using:

• PowerShell (5.1.14409.1018), 
• ORACLE Database (12.2.0.1.0), 
• Prometheus (2.19.2), 
• Grafana (7.1.1)

PowerShell allows to perform database queries i.e. from ORACLE DB.

Following the instructions at Use Oracle ODP.NET and PowerShell to Simplify Data Access and Oracle Data Provider for .NET / ODP.NET connection strings leads to the following (working) PowerShell Script:

# Load Oracle.ManagedDataAccess.dll module in PowerShell
Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\Oracle\lib\netstandard2.0\Oracle.ManagedDataAccess.dll"

$connectionString = "Data Source=<NAME_OF_TNSNAMES_ENTRY>;User Id=<DB_USERNAME>;Password=<DB_PASSWORD>"
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

$connection.open()
$command = $connection.CreateCommand()

$command.CommandText = "
SELECT 'JOB_A' JOB_NAME, 1 STATUS FROM dual
UNION SELECT 'JOB_B' JOB_NAME, 2 STATUS FROM dual
UNION SELECT 'JOB_C' JOB_NAME, 3 STATUS FROM dual
UNION SELECT 'JOB_D' JOB_NAME, 4 STATUS FROM dual
UNION SELECT 'JOB_E' JOB_NAME, 5 STATUS FROM dual
UNION SELECT 'JOB_F' JOB_NAME, 6 STATUS FROM dual
UNION SELECT 'JOB_G' JOB_NAME, 7 STATUS FROM dual
UNION SELECT 'JOB_H' JOB_NAME, 8 STATUS FROM dual
UNION SELECT 'JOB_I' JOB_NAME, 9 STATUS FROM dual
UNION SELECT 'JOB_J' JOB_NAME, 10 STATUS FROM dual
"

$queryResult = $command.ExecuteReader()

while ($queryResult.Read()) {
$queryResult.GetOracleString(0).Value
$queryResult.GetOracleDecimal(1).Value
}

$connection.close()

When I execute this PowerShell script, I get the following output (as expected):
PS C:\Windows\system32> C:\Program Files\powershell_prom_client\test_oracle_query.ps1
JOB_A
1
JOB_B
2
JOB_C
3
JOB_D
4
JOB_E
5
JOB_F
6
JOB_G
7
JOB_H
8
JOB_I
9
JOB_J
10

Due to the fact that my query (in real world) contains several thousand rows of output, I would like to pursue a generic approach to avoid setting a separate PowerShell variable for each table cell data...

But currently I don't know what data structure is necessary or how to parse my results (JOB_NAMES (column 1) and STATUS (column 2)) in order to trasmit them subsequently to Prometheus, so that Prometheus "understands" that there is not only one single value arriving simultaneously but mutltiple values (table data) simultaneously instead...

PS: When I would be able to receive the database query data as table data in Prometheus, I would like to use Grafana's Bar Gauge visualization. Grafana's Bar Gauge in the context of MySQL exporter supports the possibility to generically repeat every column value of type string of a specific datatable column as display name (like in my case every JOB_NAME) and it's corresponding value (like in my case STATUS). See for more information Bar Gauge : How to show Series name from a query’s row value (MySQL)?

Thanks for your help in advance!

[Christian Hoffmann]

On 8/3/20 8:34 AM, 'Píer Bauer' via Prometheus Users wrote:
> Due to the fact that my query (in real world) contains several thousand
> rows of output, I would like to pursue a generic approach to avoid
> setting a separate PowerShell variable for each table cell data...
>
>
> But currently I don't know what data structure is necessary or how to
> parse my results (JOB_NAMES (column 1) and STATUS (column 2)) in order
> to trasmit them subsequently to Prometheus, so that Prometheus
> "understands" that there is not only one single value arriving
> simultaneously but mutltiple values (table data) simultaneously instead...

Not sure I understand completely, but I'll try:

If you are looking for a way to get the status of multiple jobs into
Prometheus in a dynamic way (i.e. rows), then this should be possible. I
assume you are looking for labels.

You could create output such as:
job_status{job_name="a"}

(If possible, it might make sense to choose another word than "job" as
this is a term which is already used in Prometheus in the form of
"scrape jobs").

If you are looking for a way to get multiple datapoints for the same job
(i.e. columns) into Prometheus, then this will get harder. Prometheus is
designed to scrape exactly one data point per metric per scrape.
Everything else would be considered backfilling, which is not yet ready
for use for regular users, as far as I understand).

Kind regards,
Christian