What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

process several lines from sql tablespace occupation query

Votes:

0

Hello support,

I want to ask you how to process a result from a sql query which retrieve all tablespace name which occupation like this :

TABLESPACE_NAME                  SIZE(MB)   USED(MB)    MAX_EXT PCT_MAX_SIZE
------------------------------ ---------- ---------- ---------- ------------
tbs_1                       411133.94  391752.13  430077.89           91
tbs_2                        30720      18890      20480           92
                  

The query returns all tablespaces where there occupation exceed 90% (several rows).

I would like to know how to proceed in prtg to deal with all the rows of the results and retrieve messages like :

'warning the tablespace tbs_1 is 91% full' 'warning the tablespace tbs_2 is 92% full'

Thank you very much for your help.

Regards.

Loubia

oracle prtg sql

Created on Sep 25, 2020 1:44:02 PM



4 Replies

Votes:

0

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.

Created on Sep 25, 2020 1:59:03 PM by  Florian Lesage [Paessler Support]



Votes:

0

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

Created on Sep 30, 2020 8:21:40 AM



Votes:

0

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.

Created on Sep 30, 2020 12:11:40 PM by  Florian Lesage [Paessler Support]



Votes:

0

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

Created on Sep 30, 2020 1:05:47 PM




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.