Many of us who use EM CLI to write scripts and automate our daily tasks should not miss out on the new list verb released with Oracle Enterprise Manager 12.1.0.3.0. The combination of list and Jython based scripting support in EM CLI makes it easier to achieve automation for complex tasks with just a few lines of code. Before I jump into a script, let me highlight the key attributes of the list verb and why it’s simply excellent!

1. Multiple resources under a single verb:
A resource can be set of users or targets, etc. Using the list verb, you can retrieve information about a resource from the repository database.

Here is an example which retrieves the list of administrators within EM.
Standard mode
$ emcli list -resource="Administrators"

Interactive mode
emcli>list(resource="Administrators")
The output will be the same as standard mode.

Standard mode
$ emcli @myAdmin.py
Enter password :  ******
The output will be the same as standard mode.

Contents of myAdmin.py script
login()
print list(resource="Administrators",jsonout=False).out()

To get a list of all available resources use
$ emcli list -help

With every release of EM, more resources are being added to the list verb. If you have a resource which you feel would be valuable then go ahead and contact Oracle Support to log an enhancement request with product development. Be sure to say how the resource is going to help improve your daily tasks.

2. Consistent Formatting:
It is possible to format the output of any resource consistently using these options:

  –column
  This option is used to specify which columns should be shown in the output.

Here is an example which shows the list of administrators and their account status
$ emcli list -resource="Administrators" -columns="USER_NAME,REPOS_ACCOUNT_STATUS"

To get a list of columns in a resource use:
$ emcli list -resource="Administrators" -help


You can also specify the width of the each column. For example, here the column width of user_type is set to 20 and department to 30.
$ emcli list -resource=Administrators -columns="USER_NAME,USER_TYPE:20,COST_CENTER,CONTACT,DEPARTMENT:30"

This is useful if your terminal is too small or you need to fine tune a list of specific columns for your quick use or improved readability.

  –colsize
  This option is used to resize column widths.
Here is the same example as above, but using -colsize to define the width of user_type to 20 and department to 30.
$ emcli list -resource=Administrators -columns="USER_NAME,USER_TYPE,COST_CENTER,CONTACT,DEPARTMENT" -colsize="USER_TYPE:20,DEPARTMENT:30"

The existing standard EMCLI formatting options are also available in list verb. They are:
-format="name:pretty" | -format="name:script” | -format="name:csv" | -noheader | -script

There are so many uses depending on your needs. Have a look at the resources and columns in each resource. Refer to the EMCLI book in EM documentation for more information.

3. Search:
Using the -search option in the list verb makes it is possible to search for a specific row in a specific column within a resource. This is similar to the sqlplus where clause. The following operators are supported:
          =
           !=
           >
           <
           >=
           <=
           like
           is (Must be followed by null or not null)

Here is an example which searches for all EM administrators in the marketing department located in the USA.
$emcli list -resource="Administrators" -search="DEPARTMENT ='Marketing'" -search="LOCATION='USA'"

Here is another example which shows all the named credentials created since a specific date. 
$emcli list -resource=NamedCredentials -search="CredCreatedDate > '11-Nov-2013 12:37:20 PM'"
Note that the timestamp has to be in the format DD-MON-YYYY HH:MI:SS AM/PM

Some resources need a bind variable to be passed to get output. A bind variable is created in the resource and then referenced in the command. For example, this command will list all the default preferred credentials for target type oracle_database.

Here is an example
$ emcli list -resource="PreferredCredentialsDefault" -bind="TargetType='oracle_database'" -colsize="SetName:15,TargetType:15"


You can provide multiple bind variables.

To verify if a column is searchable or requires a bind variable, use the –help option. Here is an example:
$ emcli list -resource="PreferredCredentialsDefault" -help


4. Secure access
When list verb collects the data, it only displays content for which the administrator currently logged into emcli, has access.

For example consider this usecase:
AdminA has access only to TargetA.
AdminA logs into EM CLI
Executing the list verb to get the list of all targets will only show TargetA.

5. User defined SQL
Using the –sql option, user defined sql can be executed. The SQL provided in the -sql option is executed as the EM user MGMT_VIEW, which has read-only access to the EM published MGMT$ database views in the SYSMAN schema.

To get the list of EM published MGMT$ database views, go to the Extensibility Programmer's Reference book in EM documentation. There is a chapter about Using Management Repository Views. It’s always recommended to reference the documentation for the supported MGMT$ database views.  Consider you are using the MGMT$ABC view which is not in the chapter. During upgrade, it is possible, since the view was not in the book and not supported, it is likely the view might undergo a change in its structure or the data in it. Using a supported view ensures that your scripts using -sql will continue working after upgrade.

Here’s an example
  $ emcli list -sql='select * from mgmt$target'

6. JSON output support   
JSON (JavaScript Object Notation) enables data to be displayed in a collection of name/value pairs. There is lot of reading material about JSON on line for more information.

As an example, we had a requirement where an EM administrator had many 11.2 databases in their test environment and the developers had requested an Administrator to change the lifecycle status from Test to Production which meant the admin had to go to the EM “All targets” page and identify the set of 11.2 databases and then to go into each target database page and manually changes the property to Production. Sounds easy to say, but this Administrator had numerous targets and this task is repeated for every release cycle.

We told him there is an easier way to do this with a script and he can reuse the script whenever anyone wanted to change a set of targets to a different Lifecycle status.

Here is a jython script which uses list and JSON to change all 11.2 database target’s LifeCycle Property value.

If you are new to scripting and Jython, I would suggest visiting the basic chapters in any Jython tutorials. Understanding Jython is important to write the logic depending on your usecase.
If you are already writing scripts like perl or shell or know a programming language like java, then you can easily understand the logic.

Disclaimer: The scripts in this post are subject to the Oracle Terms of Use located here.

1	from emcli import *
2	search_list = ['PROPERTY_NAME=\'DBVersion\'','TARGET_TYPE=  \'oracle_database\'','PROPERTY_VALUE LIKE \'11.2%\'']
3	if len(sys.argv) == 2:
4	print login(username=sys.argv[0])
5	l_prop_val_to_set = sys.argv[1]
6	l_targets = list(resource="TargetProperties", search=search_list,    columns="TARGET_NAME,TARGET_TYPE,PROPERTY_NAME")
7	for target in l_targets.out()['data']:
8	t_pn = 'LifeCycle Status'
9	print "INFO: Setting Property name " + t_pn + " to value " +       l_prop_val_to_set + " for " + target['TARGET_NAME']
10	print  set_target_property_value(property_records=       target['TARGET_NAME']+":"+target['TARGET_TYPE']+":"+       t_pn+":"+l_prop_val_to_set)
11	else:
12	print "\n ERROR: Property value argument is missing"
13	print "\n INFO: Format to run this file is filename.py <username>   <Database Target LifeCycle Status Property Value>"

You can download the script from here. I could not upload the file with .py extension so you need to rename the file to myScript.py before executing it using emcli.

A line by line explanation for beginners:

Line
1	Imports the emcli verbs as functions
2	search_list is a variable to pass to the search option in list verb. I am using escape character for the single quotes. In list verb to pass more than one value for the same option, you should define as above comma separated values, surrounded by square brackets.
3	This is an “if” condition to ensure the user does provide two arguments with the script, else in line #15, it prints an error message.
4	Logging into EM. You can remove this if you have setup emcli with autologin. For more details about setup and autologin, please go the EM CLI book in EM documentation.
5	l_prop_val_to_set is another variable. This is the property value to be set. Remember we are changing the value from Test to Production. The benefit of this variable is you can reuse the script to change the property value from and to any other values.
6	Here the output of the list verb is stored in l_targets. In the list verb I am passing the resource as TargetProperties, search as the search_list variable and I only need these three columns – target_name, target_type and property_name. I don’t need the other columns for my task.
7	This is a for loop. The data in l_targets is available in JSON format. Using the for loop, each pair will now be available in the ‘target’ variable.
8	t_pn is the “LifeCycle Status” variable. If required, I can have this also as an input and then use my script to change any target property. In this example, I just wanted to change the “LifeCycle Status”.
9	This a message informing the user the script is setting the property value for dbxyz.
10	This line shows the set_target_property_value verb which sets the value using the property_records option. Once it is set for a target pair, it moves to the next one. In my example, I am just showing three dbs, but the real use is when you have 20 or 50 targets.

The script is executed as:
$ emcli @myScript.py subin Production

The recommendation is to first test the scripts before running it on a production system. We tested on a small set of targets and optimizing the script for fewer lines of code and better messaging.

For your quick reference, the resources available in Enterprise Manager 12.1.0.4.0 with list verb are:
$ emcli list -help


Watch this space for more blog posts using the list verb and EM CLI Scripting use cases. I hope you enjoyed reading this blog post and it has helped you gain more information about the list verb. Happy Scripting!!

Disclaimer: The scripts in this post are subject to the Oracle Terms of Use located here.

Stay Connected: Twitter | Facebook | YouTube | Linkedin | Newsletter mt=8">Download the Oracle Enterprise Manager 12c Mobile app