CA Service Management

Hi,

Can I get a query to check which are the offerings for which eem group permission is set.Instead of searching in service and click on permission tab.  

Good Morning Shubha.

Please check on the below and let me know your findings.

Thanks and kind regards, Louis van Amelsfort.

What happens when you alter permissions of a service offering or SOG or a service option?

First of all, permissions are entirely persisted in EEM.

When you change permissions of a service offering, it creates an ACL in offering folder on EEM and attached all the affected resources(Service offering, SOG and Service option).

For example, by assigning permission to a group 'Domain Admins' for the service offering 'Monitor accessories', the following ACLs are created. (Refer to screenshot at bottom too),

The code finds the list of all Rate plans (SOG) and service options, using the following queries:

SQL> select id, child_id from usm_offering_rplan_inclusion where parent_id = 10060;

ID CHILD_ID ---------- ---------- 10043 10045 10042 10046 10041 10047 10044 10048

SQL> select usm_offering_ratedef_inclusion.child_id id

from usm_offering_ratedef_inclusion,usm_rate_definition

where usm_offering_ratedef_inclusion.child_id = usm_rate_definition.item_id

and usm_rate_definition.rate_col = 0

and usm_offering_ratedef_inclusion.parent_id = 10060

and usm_offering_ratedef_inclusion.rate_plan_id = 10045;

ID ---------- 13354

SQL> select usm_offering_ratedef_inclusion.child_id id

from usm_offering_ratedef_inclusion,usm_rate_definition

where usm_offering_ratedef_inclusion.child_id = usm_rate_definition.item_id

and usm_rate_definition.rate_col = 0

and usm_offering_ratedef_inclusion.parent_id = 10060

and usm_offering_ratedef_inclusion.rate_plan_id = 10046;

ID ---------- 13555 13567 13643 13647 13684 13688 13733 13737 13741 13748 13751

ID ---------- 13758 13768 13777 13784 13788 13792 13796 13800 13810 13814 13818

ID ---------- 13831 13864 13888

25 rows selected.

SQL> select usm_offering_ratedef_inclusion.child_id id

from usm_offering_ratedef_inclusion,usm_rate_definition

where usm_offering_ratedef_inclusion.child_id = usm_rate_definition.item_id

and usm_rate_definition.rate_col = 0

and usm_offering_ratedef_inclusion.parent_id = 10060

and usm_offering_ratedef_inclusion.rate_plan_id = 10047;

ID ---------- 13835

SQL> select usm_offering_ratedef_inclusion.child_id id

from usm_offering_ratedef_inclusion,usm_rate_definition

where usm_offering_ratedef_inclusion.child_id = usm_rate_definition.item_id

and usm_rate_definition.rate_col = 0

and usm_offering_ratedef_inclusion.parent_id = 10060

and usm_offering_ratedef_inclusion.rate_plan_id = 10048;

ID ---------- 13843

Rationale:

Catalog has a logic to create an ACL object in EEM whenever permission for a service offering changes.

These objects use a numbered sequence such as 'ACL_10001_Offerings' ACL_10002_Offerings

The next available number is stored in usm_lastid table.

For this specific case, the next available number is stored against name 'eiam_acl_id'.

This information is stored in EEM and it's not a trivial task to get this information.

When in the SC-UI for a service/offering/option and you click on the permissions tab, it goes to EEM and gathers the information from there.

This information is stored in a proprietary database for EEM.

This data is not stored in an MDB table anywhere.

Getting this information from EEM is not a trivial task, I'm afraid as this is done in the Service Catalog code.

You may be able to get this information from EEM using EEM web services, but that is outside the scope of support.

Good Morning Shubha.
 
Another information found for you. To build on this accordingly.
And to possibly retrieve your requested data from your EEM installtion.
 
1.
http://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec571301.html
 
2a.
In order to export EEM policies and settings using safex, you have to create a file with commands that tells safex to export.
Once the export command file created you will then use safex and the import function on the export command file.
2b.
Create the Export Command File sample file: C:\TEMP\EEM_Export.txt with content:
<Safex>
<Attach label="Service Catalog"/>
<!--
You can control the data to be exported by specifying the Yes(Y) or No(N).
If you store the global users and global groups in CA's Management Database (CA-MDB) all the objects are exported.
You can override the maximum number of items that are returned by the backend server.
The default is 2000. To change the maximum number of items to return, include the maxsearchsize="Value"
-->
<Export file="Service Catalog_EEM_Export.txt" globalfolders="n" globalusergroups="n" globalusers="n" globalsettings="n" folders="n" usergroups="n" users="n" calendars="n" policies="y" appobjects="n"/>
<Detach/>
</Safex>
 
3.
And then to execute the command to export by opening a commandprompt and run:
cd C:\Program Files\CA\Service Catalog\bin\safex
"safex" -h (EEM SERVER) -u EiamAdmin -p (EiamAdmin Password) -f (Import file or export command file)
safex -h amelo01-I1660 -u EiamAdmin -p interOP@1660 -f eem_export.xml
 
4.
Open the file: notepad "Service Catalog_EEM_Export.txt"
And search for the ACL_10* policies and their content.
 
Thanks and kind regards, Louis van Amelsfort.

It looks to be complicated. I was thinking its stored in usm tables which as all the information on offerings/service created. There is no simple query to exact it??

I tired to do the above step. I created a text file with EEM Export in service catalog server in E drive in temp folder.

Then from command prompt I ran the export command it give me following error

can you please tell me if I am doing anything wrong in this.

I was able to extract the reports from executing above cmd in eem

Good Afternoon Shubha.

Since this info is stored in EEM, there is not such thing as a simple query.

Once you performed step3, creating the input file, please then execute:

"C:\Program Files\CA\Service Catalog\bin\safex\safex" -h <server> -u EiamAdmin -p <pw> -f eem_export.xml

That should do it for you then.

Thanks and kind regards, Louis.