Can anyone point me to the right direction?
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,
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.
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.
That is the NR_Comment table which stores the change history for a configuration item.
Recently someone asked how to load users into this table, if that's what you're looking for you can reference the steps here.
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?
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
Thanks as always Jon. No worries.
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.
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)
Thanks gbruneau. It looks like what I have, but I'll redo it and follow step by step.
It works! Thanks!
Glad that worked out!