Cameron McFie : Hosting

Reporting Hosted CRM Users

Cam — Mon, 11 Aug 2008 09:58:00 GMT

Hi All,

Sorry for my lack of blog posts lately.

As I was doing my SPLA reports, I decided I would share with everyone the SQL query that I am starting to use to provide me a list of CRM users for both SPLA and billing.

It is a query you can run in SQL Management Studio connected to the database server storing all of your CRM databases. This would obviously be run against each of your CRM database servers.

It has three different reporting modes, all you do is change the "SELECT @ReportType = 0" line at the top accordingly. Descriptions of each of the reports are listed above that line.

I hope it helps.

Kind Regards,

Cameron.

---------------------------------------------------------------
-- Hosted CRM Reporting
-- v1.0
-- Written by Cameron McFie
--
-- General Disclaimer : Cameron McFie is not responsible for
-- any incorrect SPLA reporting by your business.
---------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @ReportType INT
---------------------------------------------------------------
-- Specify the Report type
-- 0 = Full List of all CRM databases and usernames
-- 1 = Full List of all CRM databases and count of usernames
-- 2 = Total Count of all users on the server
---------------------------------------------------------------
SELECT @ReportType = 0

--------------------------------------
-- Exclude Admin users from the list
--------------------------------------
CREATE TABLE #ExcludedUsers (DomainName nvarchar(255) PRIMARY KEY)
INSERT INTO #ExcludedUsers VALUES (N'FABRIKAM\CRMServiceAcct') -- This is the admin user that deployed all of the sites
INSERT INTO #ExcludedUsers VALUES (N'%trial%') -- This will exclude all users with trial in their sAMAccountName
DECLARE ExcludedUsersList CURSOR FOR select DomainName from #ExcludedUsers
DECLARE @ExcludedUser varchar(255)
DECLARE @ExcludedUserStr varchar(8000)
Set @ExcludedUserStr = ''
OPEN ExcludedUsersList
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM ExcludedUsersList INTO @ExcludedUser
IF (@@FETCH_STATUS=-1) break
    --SELECT @ExcludedUser
    If (@ExcludedUser LIKE '%[%]%')
    BEGIN
        Set @ExcludedUserStr = @ExcludedUserStr + 'AND DomainName not like ''' + @ExcludedUser + ''' '
    END
    Else
    BEGIN
        Set @ExcludedUserStr = @ExcludedUserStr + 'AND DomainName <> ''' + @ExcludedUser + ''' '
    END
END
DEALLOCATE ExcludedUsersList
DROP TABLE #ExcludedUsers
----------------------------------------------

-------------------------------------------------
-- Create the temporary tables to store the data
-------------------------------------------------
IF (@ReportType = 0)
CREATE TABLE #ReportTempTable(DatabaseName varchar(255), DomainName varchar(255))
ELSE IF ((@ReportType = 1) OR (@ReportType = 2))
CREATE TABLE #ReportTempTable2(DatabaseName varchar(255), NumOfUsers INT)
-------------------------------------------------

----------------------------------------------------
-- Used to make sure the database is accessible.
----------------------------------------------------
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
----------------------------------------------------

----------------------------------------------------
-- Specify the query to be run on all the databases
-- based on report type
----------------------------------------------------
declare @command1 nvarchar(2000)
IF (@ReportType = 0)
SELECT @command1 = 'USE ? INSERT INTO #ReportTempTable SELECT ''?'' AS DatabaseName,DomainName AS NumOfUsers FROM SystemUserBase WHERE DomainName is not null ' + @ExcludedUserStr + ' AND FullName <> ''SYSTEM'' AND FullName <> ''INTEGRATION'' AND IsDisabled = 0'
ELSE IF ((@ReportType = 1) OR (@ReportType = 2))
SELECT @command1 = 'USE ? INSERT INTO #ReportTempTable2 SELECT ''?'' AS DatabaseName,Count(DomainName) AS NumOfUsers FROM SystemUserBase WHERE DomainName is not null ' + @ExcludedUserStr + ' AND FullName <> ''SYSTEM'' AND FullName <> ''INTEGRATION'' AND IsDisabled = 0'
----------------------------------------------------

----------------------------------------------------
-- Declare the cursor listing all the CRM content
-- databases. This is used by sys.sp_MSforeach_worker
----------------------------------------------------
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (d.name like ''%_MSCRM'') and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )
----------------------------------------------------

----------------------------------------------------
-- Run the query on each of the databases specified
-- in the cursor above.
----------------------------------------------------
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sys.sp_MSforeach_worker @command1, N'?', null, null, 1
else
select @retval
----------------------------------------------------

----------------------------------------------------
-- Display the results of the report (Different for
-- each report type).
----------------------------------------------------
IF (@ReportType = 0)
    SELECT DatabaseName, DomainName FROM #ReportTempTable ORDER BY DatabaseName
IF (@ReportType = 1)
    SELECT DatabaseName, NumOfUsers from #ReportTempTable2 ORDER BY DatabaseName
IF (@ReportType = 2)
    SELECT Sum(NumOfUsers) AS TotalNumberOfUsers from #ReportTempTable2
----------------------------------------------------

----------------------------------------------------
-- Clean up the temporary tables.
----------------------------------------------------
IF (@ReportType = 0) DROP TABLE #ReportTempTable
ELSE IF ((@ReportType = 1) OR (@ReportType = 2)) DROP TABLE #ReportTempTable2
----------------------------------------------------

Service pointers – Yet another place MPS stores information

Cam — Thu, 15 Nov 2007 13:35:00 GMT

As an alternate database, MPS/HMC/WBH stores details about some of the related objects in Active Directory. None of these details are stored in the MPS SQL databases from what I can see.

These details are called service pointers. If you look in adsiedit.msc you will find them under the customers OU in the _Private->Services OU. They are mainly used by your Control Panel to get a limited list of objects related to the customer although it is used by the provisioning system in some cases to set attributes on users as they are created.

HMC/WBH uses this to store information about the following objects;

DNS Zones
- Stores a list of DNS zones held by the customer.
FTP Sites
- Stores the location of the FTP server where the customer's website is held
- This information is used when creating any new users. The msIIS-FTPDir and msIIS-FTPRoot attributes of a user are updated accordingly based on this object.
Sharepoint Sites
- Stores a list of Sharepoint sites held by the customer
Web Sites
- Stores a list of websites held by the customer
- Stores the "location" attribute of the website e.g. "IIS://WEB01/W3SVC/3335139"
- Used to help build the FTP path that is stored in msIIS-FTPDir attribute of the customer's users.

These are very handy to know about if you are building your own MPS namespaces.

otherWellKnownObjects – the family relatives you do actually want to know

Cam — Mon, 22 Oct 2007 07:13:00 GMT

In Active Directory there is an attribute named "otherWellKnownObjects" which is not very well documented but put simply is used to reference other related objects.

When talking about otherWellKnownObjects you will also hear about a term "Well Known GUID". Well Known GUIDs define a related object type. See the lists below for examples.

The otherWellKnownObjects attribute lists these related objects in a format like the following;

B:32:89FB25B7DF784FC198A493E2E8A0EE7E:CN=MyCompany GAL,CN=All Global Address Lists,CN=Address Lists Container,CN=HostedExchange,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=mydomain,DC=com;

Combining the Well known GUID with the DN of the object that has the otherWellKnownObjects attribute set provides you with somewhat of a lookup method. E.g. On the HMC platforms I can obtain the global address list used by the customer in the MyCompany OU by performing an LDAP bind to;

LDAP://<WKGUID=89FB25B7DF784FC198A493E2E8A0EE7E,ou=MyCompany,ou=Hosting,dc=mydomain,dc=com>

Note: Using the otherWellKnownObjects attribute is also handy to reference objects that can be moved or renamed. This works because any time an object is moved or renamed it checks any references to it in the otherWellKnownObjects attribute of other objects and updates them of the change accordingly. An MSDN article has been written about that: http://windowssdk.msdn.microsoft.com/en-us/library/ms676295.aspx

Microsoft HMC / MPS Uses of the otherWellKnownObjects attribute

On the Customer OU

Well Known GUID	Definition
D22DFCC5B73645E99E16C9AD3D61F34F	DN of the Offline Address List for this company
9E444526CB6F4D5C9A59C9A84E26B627	DN of the Address List for this company
89FB25B7DF784FC198A493E2E8A0EE7E	DN of the Global Address List
3841BDA6D81C4095B9BBB838808F5A55	DN of the CN containing the service records
4619BE598BF441DB8C9DB0482E62E386	DN of the CN listing all groups and their types
3B6FF4FA8AA248039AD8F9493A43B704	DN of CSRAdmins@Customer group
65F37ECB46704F0E9300E1FB48E1096E	DN of the Admins@customer group
EA755D448CE64157A20E82B7CCBE14B0	DN to show the organisation type (e.g. private, customer, reseller, hosting)
58888CFC8F7F430C8183102CD5758D81	DN of the parent OU
CC016CF08DEF4EA4A05C9C54B198785A	DN of the customer OU

On each user

Well Known GUID	Definition
B7AE2ABCCBAD41A28973559FCA154DB0	DN of the Customer OU

On the _Private OU

Well Known GUID	Definition
EA755D448CE64157A20E82B7CCBE14B0	DN to show the organisation type (e.g. private, customer, reseller, hosting)
CC016CF08DEF4EA4A05C9C54B198785A	DN of the Customer OU

DTMU – a handy little migration thingimebob

Cam — Mon, 15 Oct 2007 07:22:00 GMT

When migrating from Microsoft HMC 3.5 to HMC 4.0 you may come across a little utility named DTMU.exe.

For those who are interested in what the DTMU tool does or are trying to work out what it is doing in the case that it fails, here you are;

DTMU –PrepareAD

Renames the "CN=MPS Server Config Entity,CN=System,DC=mycompany,DC=com" object to "CN=MPS Server Config Entity 3.5,CN=System,DC=mycompany,DC=com".
It renames this, so the new install of HMC 4.0 thinks there is no MPS servers installed elsewhere.
This attribute "MPS Server Config" is used by the Deployment tool to find the Config share and the MSI Share. It looks at the adminDescription attribute of this object for this information.

DTMU –ResetPW

Resets the MPFServiceAcct password in Active Directory
Administrative Tools->Component Services->Computers->My Computer->COM+ Applications->Provisioning Engine properties. On the Identity tab, the password in "This user:" is changed.
Resets the password on the logon tab of the "Provisioning Auditing and Recovery Service" on the HMC 3.5 provisioning servers. Open "Services" applet from control panel (or Start->Run->services.msc) to see this.