process several lines from sql tablespace occupation query

Hello Loubia,

Thank you for your message.

Regarding what you would like to do, I would recommend to write a custom script and execute it with one of the EXE custom sensors. The script should return the total number of tablespace where the occupation exceed 90% in a channel, and displays the name of the corresponding tablespaces in the text message of the sensor.

You can find the manuals of the EXE sensors here: https://www.paessler.com/manuals/prtg/exe_script_sensor https://www.paessler.com/manuals/prtg/exe_script_advanced_sensor

The manual about Custom sensors shows the structure to follow when sending data in PRTG.

If you have further questions, don't hesitate.

Kind regards.

Hello Florian,

thank you a lot for your reply.

I'm new to PRTG, so I would like to ask you if there is a procedure or a tutorial for this type of needs which is very helpful for all the organisations which hosts Oracle databases with lots of tablespaces ?

We cannot create a channel for every tablespace per database (hundreds of tablespaces per instance) with the default oracle sensors.

I did not find a clear and detailed answer to this type of need in the forum despite the various posts on this subject.

Could you please help us ?

thank you very much,

kind regards

Hello,

Monitoring all the tablespaces with the Oracle Tablespace sensor is possible, when selecting tablespaces in the list, PRTG wil automatically create a sensor for each of them. Then, you can increase the scanning interval to 5 minutes or more (the higher, the better).

The manual of the sensor is here: https://www.paessler.com/manuals/prtg/oracle_tablespace_sensor

• Pros: Easier and natively supported in PRTG
• Cons: Creates a lot of sensors / Database

However, according to your need, I would not recommend to go that way. Indeed, you can develop a small custom script which executes the SQL query to get the list of tablespaces (including their size). Then, you only have to process the data and return the number of tablespaces whose size has exceeded thresholds you have choosen, as well as their name.

• Pros: You get only the information you need / 1 sensor only per Database
• Cons: Requires development skills

I'm afraid that we do not have any reference on similar scripts and can't provide so much support about it. However, we are happy to help regarding the implementation of the script with PRTG.

There is different ways to return data in PRTG via a custom script (depending on the type, basic or advanced), which are explained in this manual: https://www.paessler.com/manuals/prtg/custom_sensors#advanced_sensors

Kind regards.

Hello Florian,

thank you for your reply and explanations,

The SQL script is ready and returns the good values from sqlplus about tablespaces which exceed 90% (tablespace_name, occupation) so the development part is done and I use sqlsensor to execute the query.

the script result :

TABLESPACE_NAME                PCT_MAX_SIZE
------------------------------ ------------
tbs_1                                    91
tbs_2                                    92

I just need some help to treat the data with implementing threshold if it's possible and the parameters on the sensors to use because i'm facing some issues like :

Id 3: "tbs_1" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.

thank you very much Florian