What can I do with jython? Building Dynamic Scripts – OEM Target Properties – Part 2

So in this blog we expand on the previous blogs to enable a OEM group to be created and for that group to have the targets added based on certain target properties.

To recap on the original script follow this link:

http://blog.e-dba.com/blog/2014/11/10/what-can-i-do-with-jython-building-dynamic-scripts-oem-target-properties-part-1/

So the new script has a few revisions on it. I will just pull out the key parts of the Jython script.

def get_target_list():
    l_sql =  "select target_name from mgmt$target_properties where property_value  = '" +v_tier_description + "' and property_name = 'udtp_3' and target_type='oracle_database'"
    return obj

Firstly when I pull back the target names I have added an additional item to a where clause.  This ensures that when we look for the ‘Tier Names’ in the specific property category of udtp_3.  As target properties are free text there could be other targets with other properties and property values like our tier names, unlikely I know but it could happen.

The next section below takes an input for a group name and then creates the group based on the targets select.

v_group_name = raw_input("Specify Your Group Name: ")
print "You Specified: " +v_group_name

l_target_list_grp = get_target_list_grp()
for target_list_grp in l_target_list_grp.out()['data']:
tng = target_list_grp['TARGET_NAME']

print "Target String: " +tng
create_group(name=v_group_name,add_targets=tng)

However there are a couple of things to note:

  • You can’t create an empty group
  • If you want to add members to a group in one statement they have to be in the format “db1:oracle_database;db2:oracle_database”.  Therefore to get around this problem we reselect the targets using an analytic function.  This creates a list of targets into a single row with the correct syntax for the create group emcli verb.
def get_target_list_grp():
l_sql =  "select LISTAGG(target_name||':oracle_database',';') WITHIN GROUP (order by target_name) target_name from mgmt$target_properties where property_value  
= '" +v_tier_description + "' and property_name = 'udtp_3' and target_type='oracle_database'"
return obj

This is the secondary target list which as you can see uses the LISTAGG function to generate the single row.

So now we have a script which will:

  • Show a distinct list of property values against a given target type and property type, in this case oracle_database and tier
  •  Allow you to select a particular tier and will show you all the given targets
  •  Allow you to specify a name for a new group
  •  Add those targets into that group based on the property value and type

So that is how you can use emcli and Jython to make dynamic scripts and groups.

TweetMe:pbedba

 

 

 

What can I do with jython? Building Dynamic Scripts – OEM Target Properties – Part 1.5

Quick game of spot the difference…..

WTDWJ2

You didn’t see it, come on try harder!!!  I got this error while editing the script.

WTDWJ4

Having been doing a bit of Jython now I am used to it, but if you haven’t touched it before it can be a pain.  The difference is this.

WTDWJ3

Obvious it was a tab and not a space!!!  You can search for tabs in notepad++, here is how.

WTDWJ5

Part 2 coming soon.

What can I do with jython? Building Dynamic Scripts – OEM Target Properties – Part 1

In this series of blogs we are looking to improve what we can do with Jython scripting by extending beyond a simple query and action script.  The goal of this series will be to create a dynamic OEM group based on a specific database target property.

In this first blog we extract a distinct list of target property values, in this case Tiers, and then use that information to retrieve a list of targets which are in that Tier.

The whole script will be at the bottom of this post but here are the key sections described.

def get_properties_list():
    l_sql =  "select distinct property_value  from mgmt$target_properties where property_name  = 'udtp_3' order by 1 "
    obj = list(sql=l_sql)        
return obj

The query above gets a distinct list of property values based on a custom target property type. I have added a custom property type to my oracle_database targets. The custom property is called tier. However if you add in custom properties to target types in OEM they aren’t stored in the MGMT$TARGET_PROPERTIES table as the name displayed on the GUI. Therefore the internal name is UDTP_3.

def get_target_list():
    l_sql =  "select target_name from mgmt$target_properties where property_value  = '" +v_tier_description + "' and target_type='oracle_database'"
    return obj

This query then takes the Tier value specified and then brings back all the targets. Notice the value +v_tier_description (this is the input variable which you will be prompted for).

The key part of the Jython script is this next section. Firstly the execution of the first query to retrieve the different ‘Tiers’, this is displayed in a short list.

 l_property_list = get_properties_list()
for property_list in l_property_list.out()['data']:
     pv = property_list['PROPERTY_VALUE']
     print "Tier: " +pv

Then you input your Tier based on the list provided:

 v_tier_description = raw_input("Insert your value: ")
print "You Specified  " +v_tier_description

Then the Jython script returns the list of targets:

l_target_list = get_target_list()
for target_list in l_target_list.out()['data']:
     tn = target_list['TARGET_NAME']
     print "Target Name: " +tn

Here is a screenshot, albeit not very exciting, but you get the point.  We have a Jython script that you can use to select a data set and then execute a further action with that data set.

WTDWJ1

TweetMe: pbedba

Here is the whole script, if this doesn’t work tweet me as it probably is the whitespaces, I can always send it directly.

#emcli_json_processing.py
#Import all EM CLI verbs to current program
from emcli import *
def format(str):
   '''
   Given a string argument returns it back or returns 
   a blank string if it is of None type
   '''
   if str is None:
      return ""
   return str

def get_properties_list():
    l_sql =  "select distinct property_value  from mgmt$target_properties where property_name  = 'udtp_3' order by 1 "
    obj = list(sql=l_sql)                  
    return obj

def get_target_list():
    l_sql =  "select target_name from mgmt$target_properties where property_value  = '" +v_tier_description + "' and target_type='oracle_database'"
    obj = list(sql=l_sql)                  
    return obj

#Set the OMS URL to connect to
set_client_property('EMCLI_OMS_URL','YOUR_OEM_URL')
#Accept all the certificates
set_client_property('EMCLI_TRUSTALL','true')

#Log in to the OMS
login(username='sysman',password='YOUR_OEM_PASSWORD')

#Display a list of Tiers
l_property_list = get_properties_list()
for property_list in l_property_list.out()['data']:
     pv = property_list['PROPERTY_VALUE']
     print "Tier: " +pv

v_tier_description = raw_input("Insert your value: ")
print "You Specified: " +v_tier_description

l_target_list = get_target_list()
for target_list in l_target_list.out()['data']:
     tn = target_list['TARGET_NAME']
     print "Target Name: " +tn

 

 

Saving you 15 minutes: What’s on you c:\ drive and OEM Enterprise Search

The good and bad thing about Windows is that it is so easy to use, and easy to deploy Oracle on (you could argue that is a bad thing I know).  The only pre-req really is that you can use a computer.  In the large Oracle estate where a certain number of database servers are running on Windows it’s more likely, in my experience, that database file locations can start to slip if those servers aren’t locked down.  A way to quickly check how much of this is an issue on your Enterprise, is to use OEM Enterprise Search (almost..).

Here you can see the configuration search, basically tell me about any databases which have c:\ in the controlfile name:

CS1

Result, now show me controlfile and redo log files….

CS2

Oh, so why no results.  Well what occurs in the Enterprise search is that the query by default brings together the different property searches with AND statements, i.e. show me all databases with c:\ in the controlfile name AND redo log file name and we want to use an OR clause.  The simple way to work around this is to modify the existing query created by the modeller and edit this for our requirements.

CS3

So, a quick and easy way to do this was to use the views which all have the column FILE_NAME.

CM$MGMT_DB_CONTROLFILES_ECM
CM$MGMT_DB_DATAFILES_ECM
CM$MGMT_DB_REDOLOGS_ECM

CS4

Then when you save and run this search you can get a very clear estate-wide picture of this ‘anomaly’….