Request about ODBC

[Gilali]

Hi,

I have one question about the odbc test method (HMON 7.78) :

I use it to check DB oracle on SAN to see with SQL query.

All it's ok, but I want to know if it's possible to check more than one result of this query with this method.

Here is my query :

SELECT fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", mx.maxspace "Max size MB",
DECODE(mx.maxspace, 0, ROUND(100 * (df.totalspace - fs.freespace) / df.totalspace), ROUND(100 *(df.totalspace - fs.freespace) / mx.maxspace)) "Pct. Used Max" FROM
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes)) / 1048576) MaxSpace FROM dba_data_files GROUP BY tablespace_name) mx,
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace FROM dba_free_space GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name AND df.tablespace_name = mx.tablespace_name ORDER BY "Pct. Used Max" DESC ;

Result :

My request is :

Is it possible, via only this request, to control more than one value of this board ? Because I need to return values, for monitoring, of "Tablespace value" and "used MB" and "Max size MB"and "Pct. Used Max"

Thx.

Gilali

[KS-Soft Europe]

Hm. Sorry, "ODBC Query" test method does not provide such a functionality. It is designed to fetch one particular value only.
I would suggest you to use "Active Script" or "Shell Script" test methods and write some vbs or js script that uses ADODB to connect to database.

I can provide you several examples, if you like.

Regards,
Max

[Gilali]

Thank, Max, for your quick response.

Ok, if you have some examples of that, i will appreciate

Gilali

[KS-Soft Europe]

Now I'm too busy to adjust this script according your needs, but I'm sure you will be able to do it by yourself. So, the script connects to the specified MS SQL Server and query the list of databases from this server. You may copy it into some file with .js extension, e.g. db_list.js (yes, that's JS script) and execute command like: cscript db_list.js KS-Server
where KS-Server is server name, that is passed as parameter.

To make it work with oracle, you should specify your query and change connect string to allow query connect to Oracle databse.

After that, you may adjust script once more to suit "Active Script" or "Schell Script" requirements ("ScriptRes:Status:Value" into output) and use it with HostMonitor.

Here the script:

Code: Select all

// Usages: 
// db_list <server_name>

ArgObj = WScript.Arguments;

ServerName = ArgObj.Item(0);

var SQLConnection = new ActiveXObject("ADODB.Connection");
var SQLRecordSet = new ActiveXObject("ADODB.Recordset");

var adOpenForwardOnly = 0, adLockReadOnly = 1;
var adStateOpen = 1,adOpenKeyset = 1;

SQLConnection.ConnectionString = GetConnectStr(ServerName);
SQLConnection.ConnectionTimeout = 120;
SQLConnection.CommandTimeout = 300;
SQLConnection.Open;
var sql_text = 'Select name,status, ';
sql_text += '(Case name WHEN \'common\' THEN 100 ELSE 1 END) as DBOrder ';
sql_text += 'FROM master..sysdatabases ';
sql_text += 'ORDER BY  DBOrder';

if (SQLConnection.State == adStateOpen) {
    WScript.StdOut.Write ('Connected to '+ServerName+'\n');
    try {
       SQLRecordSet.Open(sql_text,SQLConnection);
       while(!SQLRecordSet.EOF){
	 	 cur_dbname = SQLRecordSet.Fields("name").Value;
		 WScript.StdOut.Write(cur_dbname);	      
         SQLRecordSet.MoveNext();
 	   	 if (!SQLRecordSet.EOF) WScript.StdOut.Write('\n');	      
       }	
       WScript.StdOut.Write('Status : Ok');	
    } catch(e) {
      WScript.StdOut.Write('Error :'+e.description);
    }				

} else {
  WScript.StdOut.Write ('Can not connect to '+ServerName);
}

SQLRecordSet.Close();
SQLRecordSet = null;
SQLConnection.Close();
SQLConnection = null;

function GetConnectStr(SName) {
   return 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;'+ 'Data Source='+SName+';';

}

P.S. Please consider foregoing script just as an example.

Regards,
Max

[Gilali]

Thanks for your help and your example
For your script, I 'll try to adapt it to our requests.

Thanks again Max.

Gilali