What Modules Do I Have and How Many of Them?

Finding out how many modules you have installed on a site, and additionally how many times those modules are used is something that you would think is very simple within DotNetNuke. However, it is a bit more complex than one would hope. By default DNN does not provide a mechanism for you to perform this kind of research, however, I have a very simple SQL Script that will allow you to see ALL modules and from there the total number of instances (in all portals) and the total number of instances that are deleted (in the recycle bin). From an administrative perspective this is a key piece of information to know.

SELECT
    DM.ModuleName,
    DM.FriendlyName,
    (SELECT COUNT(*)
     FROM Modules
       WHERE ModuleDefId = MD.ModuleDefId
    ) AS 'TotalInstances',
    (SELECT COUNT(*)
      FROM Modules
     WHERE ModuleDefId = MD.ModuleDefId
        AND IsDeleted = 1
    ) AS 'RecycleBinInstances'
FROM DesktopModules DM
    INNER JOIN ModuleDefinitions MD
        ON DM.DesktopModuleID = MD.DesktopModuleID
GROUP BY DM.ModuleName, DM.Friendlyname, MD.ModuleDefId
ORDER BY 'TotalInstances'

Just run this script and you will quickly see all modules and module counts. A few things to note, if you have multiple portals this script will give only 1 count. Additionally this script will show you ALL modules, including "host" and "admin" modules provided by the DNN core. However as a general rule this should help you with your next administration task.

Please share any comments below, for technical assistance please use my forum!

Posted by Mitchel on Tuesday, March 25, 2008

Tags: DotNetNuke, Tutorials, SQL

Comments

Mitchel, I tried this script on my portal (running v4.6.2) but got the following error. Any ideas what may be going wrong?

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'BY'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) SELECT DM.ModuleName, DM.FriendlyName, (SELECT COUNT(*) FROM Modules WHERE ModuleDefId = MD.ModuleDefId) AS 'TotalInstances', (SELECT COUNT(*) FROM Modules WHERE ModuleDefId = MD.ModuleDefId AND IsDeleted = 1) AS 'RecycleBinInstances'FROM DesktopModules DM INNER JOIN ModuleDefinitions MD ON DM.DesktopModuleID = MD.DesktopModuleIDGROUP BY DM.ModuleName, DM.Friendlyname, MD.ModuleDefIdORDER BY 'TotalInstances'

By eoghano on Thursday, April 03, 2008 at 6:18 AM

When copying the script you want to be sure that you have a space before the word Group, it appears that it was lost when you copied and pasted

By mitchel.sellers@gmail.com on Thursday, April 03, 2008 at 7:35 AM

It looks like there are multiple places that are missing a space when using FireFox.

Select Count
Inner Join
Group By
Order By

By John Mitchell on Sunday, April 13, 2008 at 9:01 AM

The folks from DNNSpired have a good query that will help with these types of things too:
http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/108/threadid/192818/scope/posts/Default.aspx

By Will on Wednesday, April 16, 2008 at 1:41 PM

I have used this module http://www.snowcovered.com/snowcovered2/Default.aspx?tabid=242&PackageID=7636

What is good about it is you can change permissions from it. The downside is that it can be buggy/slow on a large site.

Thanks,

Will

By Will Sugg on Monday, May 12, 2008 at 1:35 PM

By Will Sugg on Monday, May 12, 2008 at 1:36 PM

Should this work for any DNN? I have DNN337 and when I copy pacte run it says ' this scenario has an error "( pardon the translation )

By Julia on Thursday, June 19, 2008 at 12:45 AM

Julia,

Please see the above notes about spaces being lost, you will want to be sure that all spacing is correct, I'll try to actually correct the post later to get it working correctly.

It SHOULD work with DNN 3.3.7 if I recall correctly.

By mitchel.sellers@gmail.com on Thursday, June 19, 2008 at 4:20 AM

Here is the query, I have fixed the spacing issues:

SELECT
DM.ModuleName,
DM.FriendlyName,
(SELECT COUNT(*) FROM Modules WHERE ModuleDefId = MD.ModuleDefId) AS 'TotalInstances',
(SELECT COUNT(*) FROM Modules WHERE ModuleDefId = MD.ModuleDefId AND IsDeleted = 1) AS 'RecycleBinInstances' FROM DesktopModules DM
INNER JOIN ModuleDefinitions MD ON DM.DesktopModuleID = MD.DesktopModuleID GROUP BY DM.ModuleName, DM.Friendlyname, MD.ModuleDefId ORDER BY 'TotalInstances'

By ElMatador on Thursday, July 24, 2008 at 12:28 PM

I just updated the article to have properly formatted code

By mitchel.sellers@gmail.com on Thursday, July 24, 2008 at 12:41 PM

Click here to post a comment

RSS Feeds/Syndication

Subscribe To Blog RSS
Subscribe To Blog Updates by E-Mail
Add blog to Technorati Favorites

Donate

Show your appreciation for the content/modules made available by MitchelSellers.com by making a donation. Donations are used to assist with dedicating time to creating free content.