CA Service Management

  • 1.  How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 05, 2016 03:39 PM


    Hello, what is the best way to read the form of a Service Catalog requirement?
    Especially when there are tables in the form.

     

    if I use DB query I got the values, for example:

     

     

     

    select form_elem_name,  form_elem_value from usm_request_item_form left join usm_subscription_detail on subscription_detail_id=usm_subscription_detail.id where request_id =  ******  and subscription_type=5 order by request_id desc

     

     

    If I use webservices I can get the labelvalues, values and labels which seems great to me. But if the requirement has a table the getFormRateItemValues method does not work.
    <soapenv:Fault xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <faultcode>soapenv:Server.generalException</faultcode>
    <faultstring>Exception occurred in getFormRateItemValues.</faultstring>
    <detail>
    <ns1:exceptionName xmlns:ns1="http://xml.apache.org/axis/">com.ca.usm.soap.axisInterfaces.WebServiceException</ns1:exceptionName>
    <ns2:hostname xmlns:ns2="http://xml.apache.org/axis/">vmdeadmsdl01</ns2:hostname>
    </detail>
    </soapenv:Fault>
    I would like to get the labels of the fields using a query to the database, which table should I relate to? (Usm_form_entities)? How? What is the best way? Thank you!
     
     
     


  • 2.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Broadcom Employee
    Posted Dec 06, 2016 01:56 AM

    A Good Morning to you.

     

    Possibly, the below queries will help you further ont his?

     

    For already created/existing requests:

    First it is to retrieve the subscription_detail_id of the request:
    SELECT * FROM usm_subscription_detail
     where request_id=11861 and subscription_type=5

    From the result use the value of the column named 'id' in the next query:
    SELECT subscription_detail_id,form_elem_name,form_elem_value,is_visible
     FROM  usm_request_item_form
     where subscription_detail_id=<your id value>

    This should show all fields(form_elem_name) and their value.

    To check for the defined form fields:
    SELECT * FROM usm_form_entities where form_entity_name like '%<your service name>%'

    From the result use the value of the column named 'form_entity_parent_id' in the next query:
    SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
     FROM usm_form_entities,usm_form_component_attributes
     where form_entity_path like '%<your id>%'
       and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id

    ===========================================================================================

    To query for an offering:
    select distinct o.offering_id,o.offering_name,
           i.parent_id,i.rate_plan_id,
           rd.rate_plan_id,rd.item_text,rd.item_type,rd.text_1
    from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
    where o.offering_name = 'Order new Team Site'
      and i.parent_id = o.offering_id
      and rd.rate_plan_id = i.rate_plan_id
      and rd.item_type = 14
    order by rd.text_1
    .....
    Or in short:
    select distinct rd.text_1
    from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
    where o.offering_name = 'Order new Team Site'
      and i.parent_id = o.offering_id
      and rd.rate_plan_id = i.rate_plan_id
      and rd.item_type = 14
    order by rd.text_1


    Query the form components for the offering, copy value of column text_1 into:
    SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
    FROM usm_form_entities,usm_form_component_attributes
    where form_comp_id = '34145' (or when multiple rows/values got returned "in ('34145','34158')")
      and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id
      
    Combined query:
    select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
    from usm_form_entities fe, usm_form_component_attributes fca
    where fca.form_comp_id in (
    select distinct rd.text_1
    from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
    where o.offering_name = 'Order new Team Site'
      and i.parent_id = o.offering_id
      and rd.rate_plan_id = i.rate_plan_id
      and rd.item_type = 14)
    and fca.form_comp_id = fe.form_entity_id
    ==============================================

     

    Kind regards, Louis.



  • 3.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 06, 2016 01:29 PM

    I!! Thanks. When I run the query for a service option with two forms, it only brings me the fields of the second form.

     

    SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
    FROM usm_form_entities,usm_form_component_attributes
    where form_entity_path like '%14667%'
    and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id
    and attr_name = '_id'

     

    Or maybe the best way to get the labels is using this method getFormRateItemValues, But as I said before, if the form has a table the method fails, do you know why?

     


  • 4.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 07, 2016 02:45 PM

    Personnaly, getFormRateItemValues does not fail when i have a table. It fails when i have two radio button with the same value

     

    You should take a look in the logs.

     

    You can also raise a case with CA.



  • 5.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 07, 2016 03:03 PM

    Ok, I will create a Case.



  • 6.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 07, 2016 03:33 PM

    Can you keep us posted on CA's findings?

     

    I use getRateFormItemValue and tables everyday. May save me a headache if i'm aware of a bug



  • 7.  Re: How to correctly read table values from a request using webservices or DB Query in PAM
    Best Answer

    Posted Dec 12, 2016 03:06 PM

    After creating the case, this was the solution indicated by CA:

     

    I did some research about this issue and found that this is a defect and is solved in INCREMENTAL PATCH 2 ON R14.1.02. This fix requires you have applied CA SERVICE CATALOG R14.1.02.

    You can download and find the instructions to apply the incremental 2 in the following link:

    #RO90087 - INCREMENTAL PATCH 2 ON R14.1.02
    https://support.ca.com/irj/portal/solncdndtls?aparNo=RO90087&os=WINDOWS&fc=3&actionID=3



  • 8.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 06, 2016 09:41 AM

    You can use this query to get the content of a request :

     

    SELECT
    URIF.form_elem_value AS value,
    URIF.form_elem_name AS name,
    USD.request_id
    FROM mdb.dbo.usm_subscription_detail AS USD
    INNER JOIN mdb.dbo.usm_request_item_form AS URIF
    ON URIF.subscription_detail_id = USD.id
    where usd.request_id= PUT_YOUR_REQUEST_ID_HERE



  • 9.  Re: How to correctly read table values from a request using webservices or DB Query in PAM

    Posted Dec 07, 2016 03:17 PM

    Thanks for all of your answer, I solved it partially doing the next:

     

    first I take the form values from  usm_request_item_form

    Process.QueryForm1 = "select form_elem_name, 
    form_elem_value from usm_request_item_form
    left join usm_subscription_detail
    on subscription_detail_id=usm_subscription_detail.id
    where request_id =  "
    + Process.Request_ID + "
    and subscription_type=5 and is_visible=1 order by request_id desc"

    Result in FormsT_

     

    then, I got the id for the forms

     

     

    Process.QueryForm2  = "SELECT id, text_1
    FROM usm_subscription_detail "
    +"where request_id=" + Process.Request_ID + "
    and subscription_type=5"
    ;

     

     finally with the values for Queryform2 (Forms_text) I can get the all labels for all form objects

     

    Process.QueryForm = "select form_entity_name,attr_value " +
    "FROM usm_form_entities,usm_form_component_attributes " +
    "where form_entity_path like '%" + Process.Forms_text[0].text_1 +
    "%' and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id "+
    "and attr_name = '_id' "+
    "union all "+
    "select form_entity_name,attr_value "+
    "FROM usm_form_entities,usm_form_component_attributes "+
    "where form_entity_path like '%"+ Process.Forms_text[1].text_1 +
    "%' and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id "+
    "and attr_name = '_id'";

    Result on FormsL_

    then I create the array values with key, values and labels

     

    /*
    * Create an array of ValueMap objects that hold the form IDs/Values as a key/value pair
    *
    * Set a Process variable to hold this local array object to make the form IDs/Values accessible
    *
    */


    // declarations
    var  l_numFields = 0;
    var l_arrayFormIDsValues = new Array();

    if (Process.FormsT_.length >= 1 && Process.FormsL_.length >= 1) {
      Process.Successful__ = "TRUE";
     
      // get the number of form fields contained in the form
      l_numFields =  Process.FormsT_.length;
      //logEvent(0, "GetFormFieldsBySubsID", "Number of fields in form: " +  l_numFields);
     
      /*
           for each form field, store the field ID and the field Value in a ValueMap object
           create an array of these ValueMap objects
         */
         
     
     
     
      var substring = "|";
      var Value = "";
     
     
      for(var i = 0; i <l_numFields; i++) {
         vmap = newValueMap();
         
         Value = Process.FormsT_[i].form_elem_value;     
         if (Value.indexOf(substring) !== -1)
           Value = Value.split("|")[1]
          
          
           vmap.strKey = Process.FormsT_[i].form_elem_name;
         vmap.strValue = Value;
         
         
         
         
         for(var j = 0; j < Process.FormsL_.length; j++)
         {
           if(Process.FormsT_[i].form_elem_name == Process.FormsL_[j].attr_value )
              vmap.strLabel = Process.FormsL_[j].form_entity_name;
          
         }
         //vmap.strKey = Process[OpName].SoapResponseData.getFormRateItemValuesReturn[0].form[0].form[i].ID[0].text_;
         //vmap.strValue = Process[OpName].SoapResponseData.getFormRateItemValuesReturn[0].form[0].form[i].value[0].text_;
         //vmap.strLabel = Process[OpName].SoapResponseData.getFormRateItemValuesReturn[0].form[0].form[i].value[0].text_;
         
         l_arrayFormIDsValues[i] = vmap;
      }
     
      // set a Process variable to hold the local array object to make the form IDs/Values accessible
      Process.arrayFormResults = l_arrayFormIDsValues;
      return;
    }
    else {
      logEvent(0, "INFO",   "No values");
    }

     

     

    what do you think?