SAP IDM Tables/Views Info.

[Former Member]

Hi Experts,

We are on IDM 7.2 SP6 patch 9

We need to create a report from IDM, which needs to have below information.

Please help to know tables/views which has below information in IDM.

1.User
2.FullName
3.EmailAddress
4.DefaultCurrency.UniqueName
5.LocaleID.UniqueName
6.TimeZoneID
7.Phone
8.Fax
9.Supervisor.UniqueName
10.Supervisor.PasswordAdapter
11.ImportCtrl

12.Location

Regards

Aman

[devaprakash_b]

Hi Aman,

Kindly check idmv_value_basic or idmv_vallink_basic views for any user mskey and see whether all the required fields are available in that view. If yes, then instead of joins, please create a job use export to ascii file and pass the mskeys of the identities in the source tab via query as joins would take consume time and checks needs to be performed. I usually extract the distinct mskeys and pass it via query to the job and export the report.

Regards,

DP

[former_member201064]

Just to drop one of my queries I've used recently to extract some information into good old excel (deleted several subselects though):

select e.mcMSKEYVALUE username, e.mcMSKEY mskey, e.mcDISPLAYNAME displayname,

(select aValue from idmv_value_basic with (nolock) where MSKEY = e.mcMSKEY and attrname = 'ACCOUNTP89_222') usernameSAP,

(select aValue from idmv_value_basic with (nolock) where MSKEY = e.mcMSKEY and attrname = 'KNF_COMPANY') companyCode

from idmv_entry_simple e with (nolock) where e.mcEntryType = 'MX_PERSON'

Save that query in a view or procedure so you can easily re-use it. If you don't use MS-SQL remove the with (nolock).

Depending on how you've saved the numbers 4, 5, 6, 9, 10 and 11 you have to use the idmv_link_ext and a join inside the subselect queries. Or maybe the valuehelp table mxi_attrValuehelp.

What are the numbers 10 and 11? Never heard of them

[former_member2987]

Hi Aman,

Please make sure you're searching the forum before posting a new question.  I'm pretty sure this topic has been covered before.

Take a look at the Schema Document and others in the Documents section. Then you might be able to ask a more focused version of your question.

Matt