Hello all,

I am struggling a little trying to write a query to extract a list of all the installed modules on a site and whether it is in use or not.

I know I can extract this from the /Admin/Extensions.aspx page of each site, but I was wanting to be able set an SQL job that mails the site admins (a.k.a my team) a list of all the installed modules across our many sites once a month for easy comparison so that we can work towards more code consistency.

Does anyone already have such a query handy?

Cheers,

Matthew.

 Danke Sebastian.

I'll give that a nudge and post the script if I get it working.

Guten Nachmittag Leupold,

My need was to be able to run a single script from a single Central Management Server and get back a list of all the modules we have installed across all our brands across multiple SQL servers and get back a searchable result table similar to the Host > Extensions table.

Possibly not the most efficient script but this works without too much drama at either a single instance  or a Central Management Server level:

==================================================================================================

/*
Created by Matthew Holloway 29/09/2015 to check all DNN DB's for installed modules.
Note: if your DNN DB's use a different naming convention you will need to update the statement "IF ''?'' Like ''DNN%'' to match"

Execute on a single instance or a central management server to collate all modules installed across websites across all instances on all SQL servers.
Skips DB's in a not available state, mirroring partner state or that don't match your prefix.

*/
--Create Table
CREATE TABLE [TempDB].[dbo].[Temp_Modules](
    [DTMods FriendlyName] [nvarchar](128) NULL,
    [ModDef FriendlyName] [nvarchar](128) NULL,
    [Module Name] [nvarchar](128) NULL,
    [Description] [nvarchar](2000) NULL,
    [Installed] [nvarchar](30) NULL,
    [Version] [nvarchar](80) NULL,
    [Premium] [nvarchar](30) NULL,
    [Depndencies] [nvarchar](400) NULL,
    [Website] [nvarchar](128) NULL
) ON [PRIMARY]

GO
--Populate table
Declare @Fullquery varchar (max)
Set @Fullquery ='
IF ''?'' Like ''DNN%''
Begin
Use [?]     
    Select Distinct [?].[dbo].[DesktopModules].[FriendlyName] as ''DTMods FriendlyName''
      ,[?].[dbo].[ModuleDefinitions].[FriendlyName] as ''ModDef FriendlyName''
      ,[?].[dbo].[DesktopModules].[ModuleName] as ''Module Name''
      ,''"''+ [?].[dbo].[DesktopModules].[Description] +''"'' as Descrption
      ,case when [?].[dbo].[Modules].[ModuleDefID] IS NULL then N''No'' else N''Yes'' End as ''Installed''
      ,[?].[dbo].[DesktopModules].[Version]
      ,case when [?].[dbo].[DesktopModules].[IsPremium] = 0 then N''No'' else N''Yes'' End as ''Premium''
      ,case when [?].[dbo].[DesktopModules].[Dependencies] IS NULL then N''Nil'' else [?].[dbo].[DesktopModules].[Dependencies] end as ''Depndencies''
      ,DB_NAME() as Website

    From
    [?].[dbo].[ModuleDefinitions]
    left join [?].[dbo].[DesktopModules]
    on [?].[dbo].[DesktopModules].[DesktopModuleID]=[?].[dbo].[ModuleDefinitions].[DesktopModuleID]
    left Join [?].[dbo].[PortalDesktopModules]
    on [?].[dbo].[ModuleDefinitions].[DesktopModuleID]=[?].[dbo].[PortalDesktopModules].[DesktopModuleID]
    left join [?].[dbo].[Modules]
    on [?].[dbo].[ModuleDefinitions].[ModuleDefId]=[?].[dbo].[Modules].[ModuleDefID]
   
        Order by [?].[dbo].[DesktopModules].[ModuleName] asc

        End
'
Insert into [tempdb].[dbo].[temp_modules] Exec sp_MSforeachdb  @fullquery

--present data
Select *
from [tempdb].[dbo].[temp_modules]

--cleanup table.
drop table [TempDB].[dbo].[temp_modules]

=========================================================================================================

Vielen Dank für den Startpunkt,

   Matthew