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

[marc.see]

Can anyone point me to the right direction?

[Jon_Israel]

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.

[marc.see]

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.

[Lindsay_Estabrooks]

usp_lrel_cenv_cntref

[Jon_Israel]

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

[Lindsay_Estabrooks]

Hi Jon,

 

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

 

Cheers

[Grant Bruneau]

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.

[marc.see]

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?

[Jon_Israel]

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

[marc.see]

Thanks as always Jon. No worries.

[Grant Bruneau]

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)

[marc.see]

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

[marc.see]

It works! Thanks!

[Grant Bruneau]

Glad that worked out!