CA Service Management

  • 1.  How to get change to request relationship data from Change table?

    Posted Oct 24, 2017 03:39 AM

    To find count of changes for each category along with count of changes initiated from Requests, how to get change to request relationship data from Change table?

    As ‘chg’ object (change table) has only BREL relation with ‘cr’ object (Call_req table) as below,

     cr_list              BREL <- cr.change {change = ?}

    DYNAMIC { DOMSET cr_list; WHERE change = ? ; PARAM_NAMES id; }

    caused_cr_list       BREL <- cr.caused_by_chg {caused_by_chg = ?}

    DYNAMIC { DOMSET cr_list; WHERE caused_by_chg = ? ; PARAM_NAMES id; }

     

    And in other hand from request to change relationship data is available. As ‘cr’ object (Call_req table) has SREL relation with ‘chg’ object (change table)  as below,

    change               SREL -> chg.id DISPLAY_NAME "Change"  UI_INFO "+AUDITLOG()"

    change_f             LOCAL INTEGER UI_INFO "+AUDIT_FLAG()" ON_DB_INIT {0}

    caused_by_chg        SREL -> chg.id DISPLAY_NAME "Caused by Change Order"  UI_INFO "+AUDITLOG()"

    caused_by_chg_f      LOCAL INTEGER UI_INFO "+AUDIT_FLAG()" ON_DB_INIT {0}

     

     

    E.g.

    1. Change table

    Change Number

    Category

    Group

    11

    Minor

    FO-SD

    21

    Major

    FO-SD

    22

    Major

    FO-SD

    23

    Emergency

    FO-Infosys SD

    26

    Minor

    FO-Infosys SD

    10

    Minor

    FO-Infosys SD

    14

    Major

    FO-Infosys SD

     

    1. Request Table

    Request Number

    Related Change Number

    102

    21

    106

     

    109

    23

    204

    26

    211

     

    231

    14

     

    1. Required Change Data Table

    Group

    Category

    Count of changes

    Count of changes initiated from requests

    FO-SD

    Minor

    1

    0

     

    Major

    2

    1

    FO-Infosys SD

    Emergency

    1

    0

     

    Minor

    2

    1

     

    Major

    1

    0

     



  • 2.  Re: How to get change to request relationship data from Change table?

    Broadcom Employee
    Posted Oct 24, 2017 05:55 PM

    Hello,

     

    I believe you are asking how to tell what requests are tied to a change order.  Assuming that is what you are asking for, the call_req table contains an attribute for "change" which is the associated change order for the request.  My feeling is you would need to search all entries in the call_req table, ie:

     

    select * from call_req where change = <change order id value>

     

    The relationship is only defined on the Call_Req table.  There is no way to obtain this from the CO perspective.



  • 3.  Re: How to get change to request relationship data from Change table?

    Posted Oct 24, 2017 06:02 PM

    I think that Vidya is looking more for a count of how many change orders are in each group, in each category, and the count of how many out of those change orders were initiated from a request.  It would be a bit of a difficult query to do.  As david mentioned, you can only get this from the CR side, not the CO side.  So you would need to do some fancy joins here to join the change table with the cr table, and then cout the changes by per category, then check if those changes exist on any CR records (where the change ID exists IN the list of CR records), and if so, count it as "1".   I am not sure of the syntax off hand, I would need to really dig into the query, which I dont have the cycles to do for you at this moment, BUT, you may be able to google different parts of it to figure out how to put it all together.



  • 4.  Re: How to get change to request relationship data from Change table?

    Posted Oct 25, 2017 10:21 AM

    Hi Jon,

    Thank you for your reply. And you are right i am looking more for a count of how many change orders are in each group, in each category, and the count of how many out of those change orders were initiated from a request.

     

    CA BOXI 4.1 OOTB report has the similar report naming Change Orders Initiated by Problem-Incident , you can refer the attached pdf for the same.

     

    Customer want to create this report in Webi intelligence with different set of category attribute. I have tried many joins but not getting proper join/query to get such report data. And have no clue how CA has developed this report, without having any relationship from change to request table.

     

    Regards,

    Vidya



  • 5.  Re: How to get change to request relationship data from Change table?
    Best Answer

    Broadcom Employee
    Posted Oct 25, 2017 11:51 AM

    You can enable the following tracing in CA SDM to see the query being executed by CABI/BOXI reports

    pdm_logstat -f odbc_backend.c trace
    pdm_logstat -f sqlquery.c trace

     

    Then run the BOXI/CABI 'Change Orders Initiated by Problem-Incident' and check the latest CA SDM STDLOG (NX_ROOT\LOG folder) to see the exact query being executed by the report.

    Then run the following commands to disable the trace

    pdm_logstat -f odbc_backend.c
    pdm_logstat -f sqlquery.c



  • 6.  Re: How to get change to request relationship data from Change table?

    Posted Oct 25, 2017 03:38 PM

    Thanks Paul_Coccimiglio - that would def. get the query for Vidya on this.

    @Vidya - please let us know if that helps you at all.  That should show you the joins etc.