La requête ci-dessous fait un 'UNION' entre trois requêtes sur les vues [v_GS_INSTALLED_SOFTWARE_CATEGORIZED], [v_GS_INSTALLED_EXECUTABLE] et [v_ADD_REMOVE_PROGRAMS] pour obtenir un inventaire logiciel un peu plus "avancé".
N.B: Modifiez la variable @CollectionID si besoin pour réduire le scope des machines concernées, et éviter un nombre conséquent de ligne renvoyées.
/* --- SOFTWARES, EXECUTABLES, AND ADD-REMOVE PROGRAMS ENTRIES INVENTORY ---
NB: UNION ENTRE LES VUES:
[v_GS_INSTALLED_SOFTWARE_CATEGORIZED]
[v_GS_INSTALLED_EXECUTABLE]
[v_ADD_REMOVE_PROGRAMS]
NB: DES COLONNES SONT CREES ET POSITIONNEE A NULL POUR POUVOIR EFFECTUER LE UNION. (Meme nombre de colonnes)
v_GS_INSTALLED_SOFTWARE_CATEGORIZED
Répertorie des informations sur les applications logicielles installées sur Configuration Manager clients trouvés via Asset Intelligence.
Cette vue contient les informations contenues dans le v_GS_INSTALLED_SOFTWARE afficher et joint plusieurs autres tables pour fournir des détails supplémentaires sur les logiciels installés.
v_GS_INSTALLED_EXECUTABLE
Répertorie des informations sur les fichiers exécutables de l’application logicielle installée sur Configuration Manager clients trouvés via Asset Intelligence.
[v_ADD_REMOVE_PROGRAMS]
Combination of 32 and 64 bit programs data in 'Add Remove Programs'
*/
DECLARE @CollectionID as Varchar(8)
SET @CollectionID = 'SMS00001' --Specify the collection ID
SELECT DISTINCT
TAB.ResourceID
,TAB.ProductCode
,TAB.[Machine Name]
,TAB.OS_Name
,TAB.Publisher0
,TAB.NormalizedPublisher
,TAB.ExecutableName
,TAB.ProductName
,TAB.AddRemove_Name
,TAB.NormalizedName
,TAB.FamilyName
,TAB.CategoryName
,TAB.InstallDate
,TAB.Soft_Autostart
,TAB.EXE_FilePath
,TAB.EXE_Description
,TAB.ProductVersion
,TAB.EXE_FileVersion
FROM (
SELECT
SYST.ResourceID,
UPPER(SOFT.ProductCode0) as ProductCode,
SYST.Name0 as 'Machine Name',
-- OS Info --
OS.Caption0 as OS_Name,
-- SOFT INFO --
SOFT.Publisher0,
SOFT.NormalizedPublisher,
NULL as ExecutableName,
SOFT.ProductName0 as ProductName,
NULL as AddRemove_Name,
SOFT.NormalizedName,
SOFT.FamilyName,
SOFT.CategoryName,
SOFT.ProductVersion0 as ProductVersion,
SOFT.InstallDate0 as InstallDate,
CASE
WHEN AUTOSTART_SOFT.Product0 IS NULL THEN 'NO'
ELSE 'YES'
END as Soft_Autostart,
NULL as EXE_FilePath,
NULL as EXE_Description,
NULL as EXE_FileVersion
FROM [dbo].[v_GS_INSTALLED_SOFTWARE_CATEGORIZED] SOFT
FULL JOIN v_R_System SYST on SYST.ResourceID = SOFT.ResourceID
FULL JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
FULL JOIN [dbo].[v_GS_AUTOSTART_SOFTWARE] AUTOSTART_SOFT on AUTOSTART_SOFT.Product0 = SOFT.ProductName0 -- TO MAKE RELATION WITH AUTO-START SOFTWARE
FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
WHERE v_FullCollectionMembership.CollectionID = @CollectionID
UNION
SELECT
SYST.ResourceID,
UPPER(EXE.ProductCode0) as ProductCode,
SYST.Name0 as 'Machine Name'
-- OS Info --
,OS.Caption0 as OS_Name
,EXE.Publisher0
,NULL as NormalizedPublisher
,EXE.ExecutableName0 as ExecutableName
,EXE.Product0 as ProductName
,NULL as AddRemove_Name
,NULL as NormalizedName
,NULL as FamilyName
,NULL as CategoryName
,EXE.ProductVersion0 AS ProductVersion
,NULL as InstallDate
,NULL as Soft_Autostart
,EXE.InstalledFilePath0 as EXE_FilePath
,EXE.Description0 as EXE_Description
,EXE.FileVersion0 AS EXE_FileVersion
FROM [dbo].[v_GS_INSTALLED_EXECUTABLE] EXE
FULL JOIN v_R_System SYST on SYST.ResourceID = EXE.ResourceID
FULL JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
WHERE v_FullCollectionMembership.CollectionID = @CollectionID
UNION
SELECT
ARP.ResourceID
,UPPER(ARP.ProdID0) as ProductCode
,SYST.Name0 as 'Machine Name'
-- OS Info --
,OS.Caption0 as OS_Name
,ARP.Publisher0
,NULL as NormalizedPublisher
,NULL as ProductName
,NULL as ExecutableName
,ARP.DisplayName0 as AddRemove_Name
,NULL as NormalizedName
,NULL as FamilyName
,NULL as CategoryName
,ARP.Version0 AS ProductVersion
,CASE
WHEN ISDATE(ARP.InstallDate0) <> 0 THEN CONVERT(date,ARP.InstallDate0)
WHEN ISDATE(ARP.InstallDate0) = 0 THEN NULL
END AS InstallDate
,NULL as Soft_Autostart
,NULL as EXE_FilePath
,NULL as EXE_Description
,NULL as EXE_FileVersion
FROM [dbo].[v_ADD_REMOVE_PROGRAMS] ARP
FULL JOIN v_R_System SYST on SYST.ResourceID = ARP.ResourceID
FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
INNER JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
WHERE v_FullCollectionMembership.CollectionID = @CollectionID
) AS TAB