Building an Oracle Management Pack for OpsMgr Cross-Platform Agents, Part 2

 It accepts as configuration parameters:

• TargetSystem 
• UserName   – to be supplied by a basic authentication RunAs account
• Password  – to be supplied by a basic authentication RunAs account
• CommandTimeout – shell command timeout in seconds
• OracleHome – to be supplied by a target property populated during discovery
• OracleSID – to be supplied by a target property populated during discovery
• SQLQuery  – the SQL query, with string literals (such as single quotes) properly escaped.

The actual shell command that is passed to the Shell Command Execution Probe Action works out to be:

ORACLE_HOME=$Config/OracleHome$;export ORACLE_HOME;echo -e ‘CONNECT $Config/UserName$/$Config/Password$@$Config/OracleSID$;\nSET HEADING OFF;\n$Config/SQLQuery$’ |$Config/OracleHome$/bin/sqlplus -S /nolog | grep -v ^$

The first line, ORACLE_HOME=$Config/OracleHome$;export ORACLE_HOME is necessary because the WSMan Invoker action user context does not load a user profile and environment variables.  So, it is necessary to explicitly set the ORACLE_HOME environment variable prior to running sqlplus. 

The next few snippets set up a sqlplus script on the fly, which is then piped to sqlplus.  Using echo -e with the \n line break instruction, the sqlplus ‘script’ becomes: 

CONNECT $Config/UserName$/$Config/Password$@$Config/OracleSID$;
SET HEADING OFF;
$Config/SQLQuery$

This is piped to sqlplus with the -S silent mode option, and /nolog to allow login credentials to be passed via a subsequent command, as opposed to the initial command line.  The |grep -v ^$ command at the end of the sequence will filter out blank lines.

Given that this sql query probe action is executed through a shell command, and not a native module, there are a few caveats for its use that are worth nothing.  Firstly, the shell command invoke action will not return an error code that accurately reflects the status of the SQL query, so the probe action should be followed by an ExpressionFilter that parses the StdOut for a string indicating an error (e.g. //*[local-name()=”StdOut”] does not contain substring:  “Error”).  

Furthermore, the output of the SQL query is returned as a single StdOut string.  So if the SQL query will return a single value, the output can simply be used with the XPathQuery: //*[local-name()=”StdOut”].   However, if multiple rows or columns are returned, a subsequent PowerShell Property Bag Probe Action will be required to parse the output with a ForEach-Object loop (each query result row then represents an item in the object collection).  Multiple result columns could be handled in several ways, but I have been using the CONCAT operator in the SQL queries to concatenate columns into a single string, separated by a character such as a colon.  This allows for an easy string split function in the PowerShell Property Bag probe to break the row into a collection of columns during processing.

In the next post in this series, I intend to describe the discovery processes for Oracle monitored objects.