FIM 2010 R2 SP1 Reporting and System Center 2012 Service Manager

The recent release of FIM 2010 R2 SP1 added support for several new platforms.

• Server 2012
• SQL 2012
• SharePoint Foundation 2013
• System Center 2012

Full details can be found at https://technet.microsoft.com/en-us/library/jj863245(v=ws.10).aspx

The inclusion of System Center 2012 has been great, but there are certainly some "gotchas".

If you have upgraded and were previously using System Center 2010 things likely went very smooth until you try to install FIM reporting on System Center 2012 with SP1 (which was a large rev in itself).

Or if you are like me and are trying to implement on a clean install of System Center 2012 then you have no doubt run into issues. Have no fear, there are some workarounds out there that I will be sharing with you. Over a few implementations, I have noticed 2 very specific problems. The first is a bug in the Portal and Service.msi that will repeatedly tell you that you are missing a patch KB2561430, however, this is actually a hotfix that is only applicable to SCSM 2010. The second problem is a missing SQL function (that is present if you upgrade from SCSM 2010), hence why it possibly went smoothly for some folks above.

Continuous prompt for patch...

I've actually found two separate workarounds for this one. My preference if you have the luxury of doing a clean install of Service Manager, is to install SM/DW 2012 (non-SP1), then install FIM reporting on top of it. Once the Datawarehouse has been registered (all the MP's have been deployed), you can run a change install on FIM to add reporting. Just make sure to read the next section before running the datawarehouse script install :)

Now if you have an existing SM 2012 installation that is already at SP1, it's not necessarily feasible to just wipe it out and start all over of course. In this case, you will need to do an unattended installation of FIM in order to deploy reporting. This also must be on an install and not a change, a fact I spent several hours learning today. The good part is that you will still be able to use an existing FIMService DB, that is neither here nor there if you do not have one to start with.

The following is an example of a command line to do just that, and more info on unattended installations are available here https://technet.microsoft.com/en-us/library/hh322863(v=WS.10).aspx

C:\Windows\SysWOW64\msiexec.exe /i "C:\FIM\Service and Portal\Service and Portal.msi" /quiet ACCEPT_EULA=1 ADDLOCAL=CommonServices,WebPortals,FIMReporting SQMOPTINSETTING=0 SQLSERVER_SERVER=FIMDBSQL SQLSERVER_DATABASE=FIMService EXISTINGDATABASE=1 MAIL_SERVER=exchange.yourdomain.com MAIL_SERVER_USE_SSL=1 MAIL_SERVER_IS_EXCHANGE=1 POLL_EXCHANGE_ENABLED=1 CERTIFICATE_NAME=ForefrontIdentityManager SERVICE_ACCOUNT_NAME=FIMServiceAccount SERVICE_ACCOUNT_PASSWORD=******* SERVICE_ACCOUNT_DOMAIN=yourdomain SERVICE_ACCOUNT_EMAIL=svc-fimservice@yourdoman.com SERVICE_MANAGER_SERVER=SMMGMTSRV SYNCHRONIZATION_SERVER=FIMSYNCSRV SYNCHRONIZATION_SERVER_ACCOUNT=Domain\fimma SERVICEADDRESS=FIMService.yourdomain.com REGISTRATION_PORTAL_URL=https://passwordregistration.yourdomain.com FIREWALL_CONF=1 SHAREPOINTUSERS_CONF=1 REQUIRE_REGISTRATION_INFO=1 REGISTRATION_ACCOUNT_NAME=fimSSPR REGISTRATION_ACCOUNT_DOMAIN=YOURDOMAIN REQUIRE_RESET_INFO=1 RESET_ACCOUNT_NAME=fimSSPR RESET_ACCOUNT_DOMAIN=YOURDOMAIN /L*v "C:\FIM\Service and Portal\fimservicelog.txt"

Missing function...

The second issue I've run into is with the missing function this becomes a problem since two of the FIM Reporting SQL stored procedures have a dependency on it, and if it's not there you will continually see errors like the ones below.

Module name: TransformEntityRelatesToEntityFact

 Message: Cannot
find either column "ETL" or the user-defined function or aggregate
"ETL.CanContinueExecution", or the name is ambiguous.

These errors manifest themselves during the transform phase of the ETL process and I have not seen SM recover on it's own even after adding the function. With that said, it's very important to make sure that all of the management packs are deployed to the DW and that the function is present before installing the DW scripts.

The SQL query below can be used to create the function ETL.CanExecutionContinue which will allow you to start enjoying the benefits of FIM reporting!

 USE [DWRepository] 
 GO 
 
 SET ANSI_NULLS ON 
 GO 
 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 CREATE FUNCTION [etl].[CanContinueExecution] ( 
 @transformName VARCHAR(256), 
 @executionStartTime DATETIME 
 ) 
 RETURNS BIT 
 AS BEGIN 
 DECLARE @canContinue BIT = 1, 
 @executionTimeLimit INT = 30 -- minutes 
 
 -- do not continue if the time we have consumed is 
 -- more or equal to the amount of time that we are 
 -- allowed to take for this batch 
 IF(ABS(DATEDIFF(mi, @executionStartTime, GETUTCDATE())) >= @executionTimeLimit) 
 BEGIN 
 SET @canContinue = 0 
 END 
 
 RETURN @canContinue; 
 END 
 
 GO 
 

Comments

• Anonymous
  January 01, 2003
  I always found in earlier versions of FIM that you would need to patch up to the appropriate level for things to work correctly.  You might try an install at the appropriate version with a new copy of the FIMService DB, patch it up to the same level as your existing DB, then run a change install to point to the old FIMService DB.

• Anonymous
  January 01, 2003
  The comment has been removed

• Anonymous
  May 22, 2013
  It appears if you have applied a hotfix to the portal the DB is at a higher level than the SP1 install so unattended install will fail.  I have not found a work around