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.
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 ----------------------------------------------------
Theme design by Cameron McFie