CA Service Management

Expand all | Collapse all

Looking for the table that contains "Other Assigned Contacts List" in the database.

Jump to Best Answer
  • 1.  Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 09:53 AM

    Can anyone point me to the right direction?



  • 2.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 10:07 AM

    Hi Marc - can you confirm if you are talking about other assigned contacts on Configuration Items?  Or is this on a different object that you are referring to?

    Let us know,

    Jon I.



  • 3.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 10:14 AM

    That's right. "Other Assigned Contacts" on Configuration Items. I think I found the answer: USP_LREL_CENV_CNTREF. This table connects to ca_contacts table. It would be great if you can confirm.



  • 4.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 10:28 AM

    usp_lrel_cenv_cntref



  • 5.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 10:48 AM

    Thanks Lindsay.  The only other table that I found was nr_com, which seems to also show the relationship. I found that by running a pdm_logstat -f sqlclass.c MILESTONE, which will show the insert queries in the logs. I added a couple of contacts to a CI, and the query showed the following:

     

    (INSERT INTO nr_com ( attr_name, com_comment, com_dt, com_par_id, com_userid, mdr_class, mdr_name, new_value, old_value, writer_id, id ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )) Input (<attr_name:string>Assigned Contact|<com_comment:string>Insert Assigned Contact|<com_dt:time>06/14/2016 09:59:17|<com_par_id:uuid>0B164C4F382DC14882E730E17B9B0E37|<com_userid:string>ServiceDesk|<mdr_class:string>(NULL)|<mdr_name:string>(NULL)|<new_value:string>Clark, Angelina |<old_value:string>(NULL)|<writer_id:uuid>7AE9734B3FF295469A211AE4ABBA6C01|<id:int>400151)

     

    When I look at SQL I see this:

     

    So maybe its also nr_com that you might be looking for?

    See which one works better for you.

    Jon



  • 6.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 10:59 AM

    Hi Jon,

     

    That is the NR_Comment table which stores the change history for a configuration item.

     

    Cheers



  • 7.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 11:59 AM

    Hi marc.see,

     

    Recently someone asked how to load users into this table, if that's what you're looking for you can reference the steps here.



  • 8.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 03:48 PM

    Ultimately, I'm trying to modify the Xtraction data model file so I can show the Other Assigned Contacts in a report. In order for me to do that, I needed to know what the table was. I set up 2 new tables in the Xtraction data model under the CMDB data source: 1. a table for USP_LREL_CENV_CNTREF, and 2. a table for "Other Assigned Contacts" (which references ca_contact). I joined NR table from USP_LREL_CENV_CNTREF and own_resource_UUID from ca_owned_resource. Then I joined CNT from USP_LREL_CENV_CNTREF and contact_uuid from ca_contact. I'm not sure why it's not working... Could it be because I'm using the wrong type of join?



  • 9.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-14-2016 05:41 PM

    Its possible - the data model from Xtraction is a bit complex in nature, and it doesnt always match up exactly.  There are some Xtraction folks in the community that may be able to offer you a bit more help on this, but unfortunately its outside the scope of support from our side to assist with editing the data model



  • 10.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-15-2016 08:45 AM

    Thanks as always Jon. No worries.



  • 11.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.
    Best Answer

    Posted 06-14-2016 06:53 PM

    Hi marc.see,

     

    I threw this together in a test environment and documented the steps.  Let me know if you have any questions, my instructions might not be super clear as I copied/pasted from ms word.

     

    Follow these steps in the Xtraction Data Model Editors.  Before doing any work backup datamodel.dat.

    1. Create table: OTHER_ASSIGNED_CONTACTS
      1. ID: OTHER_ASSIGNED_CONTACTS
      2. Text: OTHER_ASSIGNED_CONTACTS
      3. Table: CA_CONTACT
      4. Schema: DBO
      5. Alias: OTHER_ASSIGNED_CONTACTS
      6. Mark as checked: Active
    2. Add fields: to OTHER_ASSIGNED_CONTACTS
      1. OTHER_ASSIGNED_CONTACTS_PK
        1. ID: OTHER_ASSIGNED_CONTACTS_PK
        2. Text: Other Assigned Contacts PK
        3. Expression: OTHER_ASSIGNED_CONTACTS.CONTACT_UUID
        4. Data Type: Binary
        5. Mark as checked: Active
    3. OTHER_ASSIGNED_CONTACTS
      1. ID: OTHER_ASSIGNED_CONTACTS
      2. Text: Other Assigned Contacts
      3. Expression: COALESCE(OTHER_ASSIGNED_CONTACTS.FIRST_NAME + ' ' + OTHER_ASSIGNED_CONTACTS.LAST_NAME, OTHER_ASSIGNED_CONTACTS.LAST_NAME, OTHER_ASSIGNED_CONTACTS.FIRST_NAME)
      4. Data Type: String
      5. Mark as checked: Active, Group, List, Filter
    4. INACTIVE
      1. ID: INACTIVE
      2. Text: Inactive
      3. Expression: OTHER_ASSIGNED_CONTACTS.INACTIVE
      4. Data Type: Number
      5. Mark as checked: Active
    5. Add Filter to OTHER_ASSIGNED_CONTACTS table
      1. Filter: Inactive Equals 0
      2. Type: List

     

    1. Create table USP_LREL_CENV_CNTREF
      1. ID: USP_LREL_CENV_CNTREF
      2. Text: USP_LREL_CENV_CNTREF
      3. Table: USP_LREL_CENV_CNTREF
      4. Schema: DBO
      5. Alias: USP_LREL_CENV_CNTREF
      6. Mark as checked: Active
    2. Add fields to USP_LREL_CENV_CNTREF
      1. USP_LREL_CENV_CNTREF_PK
        1. ID: USP_LREL_CENV_CNTREF_PK
        2. Text: USP LREL CENV CNTREF PK
        3. Expression: USP_LREL_CENV_CNTREF.ID
        4. Data Type: Number
        5. Mark as checked: Active
    3. NR_FK
      1. ID: NR_FK
      2. Text: NR FK
      3. Expression: USP_LREL_CENV_CNTREF.NR
      4. Data Type: Binary
      5. Mark as checked: Active
    4. CNT
      1. ID: CNT_FK
      2. Text: CNT FK
      3. Expression: USP_LREL_CENV_CNTREF.CNT
      4. Data Type: Binary
      5. Mark as checked: Active
    5. Add join from USP_LREL_CENV_CNTREF to OTHER_ASSIGNED_CONTACTS
      1. Table: USP_LREL_CENV_CNTREF
      2. Join Table: OTHER_ASSIGNED_CONTACTS
      3. Join Type: Left Outer
      4. Join conditions: CNT FK -> Other Assigned Contacts PK
    6. Add join from Configuration_Item to USP_LREL_CENV_CNTREF
      1. Table: CONFIGURATION_ITEM
      2. Join Table: USP_LREL_CENV_CNTREF
      3. Join Type: Left Outer
      4. Join conditions: Configuration Item PK -> NR FK

     

    Screenshot of USP_LREL_CENV_CNTREF (My instructions don't include Last Mod By and Last Modified Date, you can reference other tables to see how they're built.)

    Screenshot of OTHER_ASSIGNED_CONTACTS (ca_contact)



  • 12.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-15-2016 08:46 AM

    Thanks gbruneau. It looks like what I have, but I'll redo it and follow step by step.



  • 13.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-15-2016 01:52 PM

    It works! Thanks!



  • 14.  Re: Looking for the table that contains "Other Assigned Contacts List" in the database.

    Posted 06-15-2016 03:32 PM

    Glad that worked out!