Using SQL Reporting Services for MIIS Reporting

1
Using SQL Reporting Services for MIIS Reporting
Brad Turner (bturner_at_ensynch.com) Architect,
Secure Identity and Access Management
Practice http//www.identitychaos.com
2
Overview

• Why do we need reporting?
• Reporting solutions
• SRS Overview
• Mining MIIS Data
• Direct Access vs Report Provisioning
• MIIS Tables at a Glance
• Query Patterns
• Building SRS Reports for MIIS
• MIIS Sample Reports
• QA and Demo

3
The need for reporting

• Compliance Reporting - HIPAA, SOX, and DPA
• Auditing who has what access and when did they
  get it?
• Licensing how many users/mailboxes or computers
  in my environment
• Project Status how is my data aggregation
  proceeding?
• Review Pending Changes what is pending to flow
  into the ADMA?

4
Community Report Pack 2007

• http//www.camelogic.com/idchaos/MIISSolutions/RP/
  CommunityReportPack2007v1.0.zip
• All MIIS Implementations should be able to
  generate the following minimum reports
• Connected Display a report of connectors based
  on MA
• Connector List subreport Display the type and
  status for each connector to the mv identity
• Disconnected Display a report of disconnectors
  based on MA
• Status Display a report based on the status of
  the identity (active, terminated, etc)
• Identity Display extended information about a
  specific mv identity

5
MIIS Dependency Matrix
Pre-SP1 Only as a connected data source, not
as the metaverse, Post-SP2 as both Only if
the miiserver.exe.config is set to force 1.1
CLR Can be used to report against, but must
be hosted in SQL 2005
6
SRS Overview

• Provides a method for generating reports based on
  data in SQL repositories
• Reports can be consumed directly through a web
  portal, extracted to various formats, or
  subscribed to through email
• SRS requires a SQL server to host the reports and
  reports server databases all data can come from
  SQL servers on or off the same box
• Licensing is included in your SQL Server license
  as long as the Reports and ReportServer dbs are
  hosted on the same server as your data
  otherwise you need a separate license for them

7
Mining MIIS Data

• Microsoft says they dont support direct access
  to the metaverse for the following reasons
• Reads can lock the records and cause MIIS to
  timeout or fail
• Writes compromise referential integrity of the
  data and can corrupt relationships
• Microsoft reserves the right to alter the db
  schema (currently unpublished)
• All of these can be mitigated in the following
  manners
• Ensure that all queries against the metaverse are
  using the WITH (nolock) directive to prevent
  inadvertent record locks
• When working with stored procedures, use SET
  TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  once instead of per table WITH (nolock)
• Never write anything directly to the MIIS tables
• Reports are not immutable, queries can be changed
  in the unlikely event the schema is modified OR
  you can provision data into a completely separate
  table to report against.

8
Direct Access vs Report Provisioning

• If accessing the metaverse is considered too
  risky, then provisioning the relevant data to a
  new table or reporting against XML drop files are
  the only other options available however the
  following cons are immediately evident
• Report Provisioning
• The sync and provisioning overhead is at least as
  much, if not more, as the read overhead from
  direct queries
• Only provisioning to an entirely separate SQL
  Server would buffer any performance impact from
  reporting but the increased costs in licensing
  may be prohibitive to some
• Adding new data to the report requires new
  attribute flow or in some cases new provisioning
  code to be written if new object types are
  required.
• Data on disconnectors cannot be provisioned
  without projecting them which compromises their
  ability to join the correct MVobject in the
  future

9
Direct Access vs XML Drop Files

• Setting your Run Profiles to create XML Drop
  Files avoids the Direct Query problem, but
• XML Drop Files
• These files are overwritten every time the MA is
  overwritten so the chance for reading an
  incomplete data set is very high (especially on
  MAs that are run frequently)
• You must understand XML query syntax to build the
  queries and cannot use SQL to mine the data
• The XML drop files must be shared via HTTP for
  SRS to read them

10
MIIS Tables at a Glance
The following tables will be referenced
throughout the presentation, however this does
not represent the entire list of tables
11
Components of an SRS Report

• Data (SQL Query) The query that returns the
  data set you wish to report on
• Layout (Form) WYSIWYG editor to drag and drop
  fields, tags and controls for displaying the data
  from your query set
• Preview Allows you to preview the form and run
  the query

12
Building a Shared Data Source
Shared Data Sources allow many reports to utilize
the same connection and credentials
13
Building an XML Shared Data Source
XML Data Sources must be hosted using HTTP
14
Defining Report Parameters
Report Parameters are used to define filters for
the query at runtime like choosing the source
MA to filter the query by at runtime
15
Building the SQL Query

• Must have a SQL Data source to connect to (either
  discrete or Shared)
• You can specify multiple queries in the query
  editor and preview the results
• Use Report Properties to specify data you wish
  the user to enter or choose from a drop-down list
  (populated by one of your other queries)

16
Designing Basic Forms

• SRS allows for only a few basic controls in your
  form, of which the following are essential
• Textbox
• Table
• Subreport
• Use the table control to display results of your
  query in a tabular form, textboxes to describe
  individual fields and the subreport to embed
  other related reports.

17
Working with subreports

• Reports can contain hyperlinks to other reports
• Reports can contain embedded reports called
  subreports
• CAVEAT When passing parameters like the
  object_ID to a subreport you need to convert it
  to string

18
Query Patterns - Connected
6A9B2344-
146F9FB8-..
6A9B2344-
6A9B2344-
146F9FB8-..
146F9FB8-..
19
Sample Query - Connectors
Connected Query

• SELECT
• mms_management_agent.ma_name, mms_metaverse.uid,
  mms_metaverse.dn, mms_metaverse.employeeID,
• mms_metaverse.employeeStatus, mms_metaverse.l
• FROM
• mms_management_agent WITH (nolock) INNER JOIN
• mms_connectorspace WITH (nolock) ON
  mms_management_agent.ma_id mms_connectorspace.ma
  _id INNER JOIN
• mms_csmv_link WITH (nolock) ON
  mms_connectorspace.object_id mms_csmv_link.cs_ob
  ject_id INNER JOIN
• mms_metaverse WITH (nolock) ON
  mms_csmv_link.mv_object_id mms_metaverse.object_
  id
• WHERE
• (mms_metaverse.object_type N'person') AND
  (mms_management_agent.ma_name _at_MA)
• ORDER BY
• mms_metaverse.uid, mms_metaverse.dn

ManagementAgents Query
SELECT ma_id, ma_name FROM
mms_management_agent WITH (nolock) ORDER
BY mms_management_agent.ma_name
20
Sample Form - Connected
Fields from any of the queries can be used in the
form layout
Text boxes
Field expression
Fields available from the query
Hyperlinked field to another report which passes
the data as a parameter
Table control
21
Sample Report Connected
22
Query Patterns - Disconnected
23
Sample Query Disconnected (Common Table
Expression)

• WITH Manager(object_id, pobject_id, rdn, dn,
  partition_id, connector_state, disconnection_modif
  ication_date, last_import_modification_date ) AS
• (SELECT object_id, pobject_id, rdn,
  CONVERT(varchar(255), rdn) AS dn, partition_id,
  connector_state, disconnection_modification_date,
  last_import_modification_date
• FROM mms_connectorspace WITH
  (nolock)
• WHERE (pobject_id
  '00000000-0000-0000-0000-000000000000') AND
  (mms_connectorspace.ma_id _at_MAID)
• UNION ALL
• SELECT e.object_id, e.pobject_id,
  e.rdn, CONVERT(varchar(255), e.rdn ',' d.dn)
  AS dn, e.partition_id, e.connector_state,
  e.disconnection_modification_date,
  e.last_import_modification_date
• FROM mms_connectorspace AS e WITH
  (nolock) INNER JOIN
• Manager AS d ON
  e.pobject_id d.object_id
• WHERE (e.is_connector 0) AND
  (e.ma_id _at_MAID)
• )
• SELECT object_id, pobject_id, dn,
  partition_name, connector_state,
  disconnection_modification_date,
  last_import_modification_date
• FROM Manager AS Manager_1 INNER JOIN
• mms_partition WITH (nolock)
  ON Manager_1.partition_id mms_partition.partitio
  n_id

Thanks to David Lundell for the idea to use the
CTE!
24
Disconnected Form

• Uses a Group to organize by partition
• Indents each RDN based on the depth of the
  hierarchy

25
Sample Report - Disconnected
26
Query Patterns - Status
27
Sample Query Status

• SELECT
• mms_metaverse.employeeID, mms_metaverse.employeeS
  tatus, mms_metaverse.uid, mms_metaverse.terminatio
  nDate, mms_metaverse.displayName,
  mms_metaverse.hireDate
• FROM
• mms_management_agent WITH (nolock) INNER JOIN
• mms_connectorspace WITH (nolock) ON
  mms_management_agent.ma_id mms_connectorspace.ma
  _id INNER JOIN
• mms_csmv_link WITH (nolock) ON
  mms_connectorspace.object_id mms_csmv_link.cs_ob
  ject_id INNER JOIN
• mms_metaverse WITH (nolock) ON
  mms_csmv_link.mv_object_id mms_metaverse.object_
  id
• WHERE
• (mms_metaverse.object_type N'person') AND
  (mms_management_agent.ma_name _at_MAName) AND
• (mms_metaverse.employeeStatus
  LIKE _at_Status)

28
Sample Report - Status
29
Creating a Connected Identity Report

• Create a central report by which others link to
  it passing the unique key that the query will use
• This is the detailed report on a single mvobject
  and its connectors

subreport
30
Sample Query Identity
SELECT uid, cn, description, department,
division, employeeID, employeeStatus,
employeeType, givenName, mail, sn, title,
object_id, displayName, info,
terminationDate, userAccountControl, hireDate,
company, physicalDeliveryOfficeName FROM
mms_metaverse WITH (nolock) WHERE
(employeeID _at_EMPID)
31
Sample Report - Identity
32
Query Patterns Connector List
33
Sample Query Connector List

• SELECT
• mms_metaverse.uid, mms_management_agent.ma_name,
  mms_connectorspace.rdn, mms_lineage_cross_referenc
  e.rule_type, mms_metaverse.employeeStatus,
  mms_connectorspace.last_import_modification_date,
  mms_connectorspace.last_export_modification_date,
  
• mms_csmv_link.lineage_date,
  mms_lineage_cross_reference.creation_date,
  mms_connectorspace.connector_state
• FROM
• mms_lineage_cross_reference WITH (nolock) RIGHT
  OUTER JOIN
• mms_csmv_link WITH (nolock) INNER JOIN
• mms_metaverse WITH (nolock) ON
  mms_csmv_link.mv_object_id mms_metaverse.object_
  id INNER JOIN
• mms_management_agent WITH (nolock) INNER JOIN
• mms_connectorspace WITH (nolock) ON
  mms_management_agent.ma_id mms_connectorspace.ma
  _id ON
• mms_csmv_link.cs_object_id mms_connectorspace.o
  bject_id ON mms_lineage_cross_reference.lineage_id
  mms_csmv_link.lineage_id
• WHERE
• (mms_metaverse.object_id _at_MVGUID)

34
Bonus Reports

• Two ways to approach rendering an
  employee/manager relationship
• These reports are not currently part of the
  Community Report Pack

35
Query Patterns Metaverse Reference
36
Sample Query Metaverse Reference

• SELECT
• dbo.mms_metaverse.displayName,
  mms_metaverse_1.displayName AS manager
• FROM
• dbo.mms_mv_link WITH (nolock) INNER JOIN
• dbo.mms_metaverse WITH (nolock) ON
  dbo.mms_mv_link.object_id dbo.mms_metaverse.obje
  ct_id INNER JOIN
• dbo.mms_metaverse AS mms_metaverse_1 WITH
  (nolock) ON dbo.mms_mv_link.reference_id
  mms_metaverse_1.object_id
• WHERE
• (dbo.mms_mv_link.attribute_name N'manager')

37
Query Patterns Connector Reference (Reference
Reprocessing Scenario)
38
Sample Query Connector Reference

• SELECT
• TOP 100 PERCENT
• dbo.mms_metaverse.displayName,
  mms_metaverse_1.mhsDisplayName,
  mms_metaverse_1.displayName AS manager
• FROM
• dbo.mms_cs_link WITH (nolock) INNER JOIN
• dbo.mms_csmv_link WITH (nolock) ON
  dbo.mms_cs_link.object_id dbo.mms_csmv_link.cs_o
  bject_id INNER JOIN
• dbo.mms_metaverse WITH (nolock) ON
  dbo.mms_csmv_link.mv_object_id
  dbo.mms_metaverse.object_id INNER JOIN
• dbo.mms_csmv_link AS mms_csmv_link_1 WITH
  (nolock) ON dbo.mms_cs_link.reference_id
  mms_csmv_link_1.cs_object_id INNER JOIN
• dbo.mms_metaverse AS mms_metaverse_1 WITH
  (nolock) ON mms_csmv_link_1.mv_object_id
  mms_metaverse_1.object_id
• WHERE
• (dbo.mms_cs_link.attribute_name N'manager')
  AND (dbo.mms_metaverse.displayName IS NOT NULL)

39
XML Data Query ADMA Export Errors

• run-history /run-details ma-name,run-profile-na
  me/step-details _at_step-number,start-date,end-date
  ,step-result,step-descriptionstep-type_at_type/s
  ynchronization-errors/export-error
  _at_dn,error-type/cd-error error-code,error-litera
  l,server-error-detail

40
XML Data Query ADMA Export Errors
41
Summary

• Reporting against the metaverse can be safe and
  provides an effective method of obtaining
  information regarding disconnectors
• Reports can be embedded through the use of
  subreports which can create a nice dashboard
  effect when viewing identities
• Report parameters can be used to provide an
  interactive experience
• Experiment!!
• Dont forget to attend David Lundells sessions
  on SQL Server.

42
Questions and Answers
http//www.identitychaos.com http//www.camelogic.
com/idchaos/MIISSolutions/RP/CommunityReportPack20
07v1.0.zip http//www.camelogic.com/idchaos/Presen
tations/UsingSQLReportingServicesforMIISReporting.
pps