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

In the first of my posts regarding development of an UNIX/Linux Cross-Platform management pack for monitoring of Oracle, I described the creation of two custom probe action modules for WSMan Invoke Action and Shell Command Execution operations.  In this post, I will describe the creation of a third custom Probe Action for executing SQL queries locally with sqlplus.  It was a bit of a challenge to get this probe action functioning the way I had intended, but after some trial and error, it does seem to work quite nicely.  The SQL Query probe action is really  just a convenient wrapper for the previously discussed ShellCommand probe action. 

 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.

About these ads

About Kristopher Bash
Kris is a Senior Program Manager at Microsoft, working on UNIX and Linux management features in Microsoft System Center. Prior to joining Microsoft, Kris worked in systems management, server administration, and IT operations for nearly 15 years.

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

  1. Pingback: Building an Oracle Management Pack for OpsMgr Cross-Platform Agents, Part 3 « Operating-Quadrant

  2. Pingback: Oracle Management Pack for OpsMgr SCX Agents, Part 4: File System and Process Monitoring « Operating-Quadrant

  3. Pingback: Finishing the Oracle SCX Management Pack for OpsMgr Cross-Platform Agents « Operating-Quadrant

  4. Allen says:

    Looking forward to episode 3!

  5. Wateruning says:

    Thanks for the idea! While using the shell command with sqlplus wrapped in, it might be necessary to add “SET LINESIZE NNNN;” to aviod unexpected line breaks in the result.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 49 other followers

%d bloggers like this: