search

jobec / powershell-prom-client

Prometheus instrumentation library for Powershell applications
BSD 2-Clause "Simplified" License
29 stars 10 forks source link

Transmission of (Database) Table Data to Prometheus #1

Open pebauer opened 4 years ago

pebauer commented 4 years ago

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...

A) Does powershell-prom-client support the transmission of table data to Prometheus? B) And if so, what do I have to do to solve my problem?

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!

DennisL68 commented 1 year ago

This is strictly not related to this module. You'd have better chance of getting a good answer in forums such as stackoverflow instead.

However, I just happened to use a more generic code, that might help you, when I was evaluating this module at first, see My proof of concept doesn't work