PI Services

Le blog des collaborateurs de PI Services

SCCM - SQL Query - Inventaire Software, Executables et AddRemove Programs

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

 

 

 

 

 

Ajouter un commentaire

Loading