none
FIMservice database structure for SQL reporting purpose RRS feed

  • Question

  • Hi all,

    To get more detail users and groups reports in FIM portal, we are planning to use SQL reporting to generate the reports. Can anyone let me know where we can find tech reference documents for FIM database structure? At least the tables which contain user and group information.

    Thanks a lot!

     

     

    Thursday, April 1, 2010 7:20 AM

Answers

  • The FIM database structure is extremely complex, and the product group doesn't support queries against the FIM Service database. In fact they strongly advise against it.

    There are about 7 tables that contain the information and getting at it is complex, plus the product group could change the schema later on.


    David Lundell www.ilmBestPractices.com
    Thursday, April 1, 2010 8:24 AM
  • correct. The only supported way is to query through WebService
    Thursday, April 1, 2010 8:34 AM

All replies

  • The FIM database structure is extremely complex, and the product group doesn't support queries against the FIM Service database. In fact they strongly advise against it.

    There are about 7 tables that contain the information and getting at it is complex, plus the product group could change the schema later on.


    David Lundell www.ilmBestPractices.com
    Thursday, April 1, 2010 8:24 AM
  • correct. The only supported way is to query through WebService
    Thursday, April 1, 2010 8:33 AM
  • correct. The only supported way is to query through WebService
    Thursday, April 1, 2010 8:34 AM
  • Thanks for the answer.

    Then is there any samples we can do reporting to FIM portal ? I didn't find sample powershell scripts in the scriptbox.

    Such as:

     

    How many users and groups of some specific department in total?

     

    How many users with the attribute A as true?

    How many users with the attribute B larger than xxx?

    How many users with the attribute C which is not empty?

     

    Thanks a lot for your help.

     

    Thursday, April 1, 2010 3:54 PM
  • like.. adding a graph in FIM Portal?

    i don't think u can

    Thursday, April 1, 2010 5:02 PM
  • You can do rudimentary reporting relative to an object. I do this to see what Sets and Groups a user is a member of:

    http://www.identitychaos.com/2008/11/ilm-2-rc0-adding-group-reporting-tab-to.html

    You should be able to apply the same controls to the RTM RCDC's. Currently there is no "out of the box" reporting through the portal. You do have other extensibility options using the web service but currently you'd have to write those extensions yourself.


    Brad Turner, ILM MVP - Ensynch, Inc - www.identitychaos.com
    Thursday, April 1, 2010 8:48 PM
  • Also check out Joe Zamora's FIMQueryTool

    http://c--shark.blogspot.com/2009/07/auditing-with-fim-query-tool.html


    David Lundell www.ilmBestPractices.com
    Friday, April 2, 2010 2:58 PM
  • You can do rudimentary reporting relative to an object. I do this to see what Sets and Groups a user is a member of:

    http://www.identitychaos.com/2008/11/ilm-2-rc0-adding-group-reporting-tab-to.html

    You should be able to apply the same controls to the RTM RCDC's. Currently there is no "out of the box" reporting through the portal. You do have other extensibility options using the web service but currently you'd have to write those extensions yourself.


    Brad Turner, ILM MVP - Ensynch, Inc - www.identitychaos.com

    Hi Brad,

     

    I tried your code in FIM 2010 RTM version. It doesn't work. After I upload the xml via RCDC and reset iis, the page cannot show and report configuration error. Can you check whether it is because of some changes in RTM or there is any thing I need to do prior to this?

     

    Thanks!

    Actionxp

    Sunday, April 4, 2010 3:54 PM
  • Support warnings aside as this is the highest ranked article if other FIM engineers stumble across this and are interested in understanding the FIMService Database Schema (Current as of 2010 R2 SP1) you can find a tutorial here:-

    http://www.esebenza.com/forefront-identity-manager/writing-custom-forefront-identity-manager-reports/

    I really do hope Microsoft provide better reporting functionality in future releases of Identity Manager or provide an export button for search scope enabled view within the FIM Portal (Simple and efficient solution).

    Hope this helps.


    • Edited by Victor J Meyer Friday, November 6, 2015 12:46 AM Spelling.
    Friday, November 6, 2015 12:45 AM
  • Hi David

    Is there any way to get email address of the person using .NET code or Database.

    I am working o now OTP smsserviceprovider.dll there I am getting mobile number by default but not email.

    Could you please help me in getting email of person based on userid.

    Thanks


    Remember Past Challenge Future"

    Monday, November 23, 2015 10:17 AM