Le blog technique

<pre class="wp-block-syntaxhighlighter-code">/* --- 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

 </pre>

	/* PATCH REQUIRED OR INSTALLED STATUS FOR ALL SERVERS WITH SOFTWARE UPDATE GROUP INFO */
	DECLARE @Collection varchar(8)
	DECLARE @StartDate DATETIME, @EndDate DATETIME
	SET @Collection = 'SMS00001' -- Collection ID for All Systems
	-- Since 1 months
	SET @StartDate = DATEADD(mm, -1,GETDATE())
	SET @EndDate = GETDATE() 
	
	SELECT DISTINCT  VRS.Name0 as 'MachineName',
	Os.Caption0 as 'OperatingSystem',
	CASE
		WHEN summ.ClientActiveStatus = 0 THEN 'Inactive' 
		WHEN summ.ClientActiveStatus = 1 THEN 'Active' 
		end as 'ClientActiveStatus',
	CASE 
		WHEN LastHealthEvaluationResult = 1 THEN 'Not Yet Evaluated' 
		WHEN LastHealthEvaluationResult = 2 THEN 'Not Applicable'
		WHEN LastHealthEvaluationResult = 3 THEN 'Evaluation Failed' 
		WHEN LastHealthEvaluationResult = 4 THEN 'Evaluated Remediated Failed' 
		WHEN LastHealthEvaluationResult = 5 THEN 'Not Evaluated Dependency Failed' 
		WHEN LastHealthEvaluationResult = 6 THEN 'Evaluated Remediated Succeeded'
		WHEN LastHealthEvaluationResult = 7 THEN 'Evaluation Succeeded'
		end as 'Last Health Evaluation Result',
	CASE 
		WHEN LastEvaluationHealthy = 1 THEN 'Pass'
		WHEN LastEvaluationHealthy = 2 THEN 'Fail'
		WHEN LastEvaluationHealthy = 3 THEN 'Unknown'
		end as 'Last Evaluation Healthy',
	CASE 
		WHEN summ.ClientRemediationSuccess = 1 THEN 'Pass' 
		WHEN summ.ClientRemediationSuccess = 2 THEN 'Fail' ELSE '' 
		end as 'ClientRemediationSuccess',
	
	
	UI.Title as 'Title',
	UI.ArticleID as 'ArticleID',
	UI.BulletinID as 'BulletinID',
	UI.DatePosted,
	UI.DateCreated,
	UI.DateLastModified,
	
	CASE UI.Severity
		WHEN 0 THEN 'None'
		WHEN 2 THEN 'Low'
		WHEN 6 THEN 'Moderate'
		WHEN 8 THEN 'Important'
		WHEN 10 THEN 'Critical'
		end as Patch_Severity,

        CASE UI.IsExpired 
		WHEN 0 THEN 'NOT EXPIRED' 
		WHEN 1 THEN 'EXPIRED' 
		END AS [EXPIRY],
	
	CASE UI.IsSuperseded 
		WHEN 0 THEN 'NOT Superseded' 
		WHEN 1 THEN 'Superseded' 
		END AS [SUPERSEDENCE],


	CASE 
		WHEN UCS.Status = 2 THEN 'Required'
		WHEN UCS.Status = 3 THEN 'Installed'
		ELSE 'Unknown' END AS 'KBStatus',
	UI.InfoURL as 'InformationURL', 
	KB_AND_SUG.Title as SoftwareUpdateGroup,

	
	CASE 
		WHEN USS.LastScanState = 0 THEN 'unknown'
		WHEN USS.LastScanState = 1 THEN 'waiting for catalog location'
		WHEN USS.LastScanState = 2 THEN 'running'
		WHEN USS.LastScanState = 3 THEN 'completed'
		WHEN USS.LastScanState = 4 THEN 'pending retry'
		WHEN USS.LastScanState = 5 THEN 'failed'
		WHEN USS.LastScanState = 6 THEN 'completed with errors'
		END as LastScanState,

	USS.ScanTime,
	USS.LastScanTime,
	USS.LastErrorCode,
	USS.LastStatusMessageID
	
	FROM v_R_System VRS
	INNER JOIN v_UpdateComplianceStatus UCS ON UCS.ResourceID = VRS.ResourceID
	INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
	INNER JOIN v_UpdateScanStatus USS on VRS.ResourceID = USS.ResourceID
	INNER JOIN v_CICategories_All CIC ON UI.CI_ID = CIC.CI_ID
	
	
	INNER JOIN v_CH_ClientSummary summ on Vrs.ResourceID = summ.ResourceID
	INNER JOIN v_GS_OPERATING_SYSTEM Os on UCS.ResourceID = Os.ResourceID
	INNER JOIN Computer_System_DATA St on UCS.ResourceID = st.MachineID
	INNER JOIN v_FullCollectionMembership Col on UCS.ResourceID = Col.ResourceID
	
/* JOINTURE POUR OBTENIR L'INFO DU SOFTWARE UPDATE GROUP AUQUEL APPARTIENS LE PATCH */
INNER JOIN (SELECT DISTINCT
		upd.ArticleID,
		AL.Title
		FROM vSMS_CIRelation as cr
		INNER JOIN fn_ListUpdateCIs(1033) upd ON  upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
		INNER JOIN v_CIToContent CC ON cc.CI_ID=upd.CI_ID
		INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
		) KB_AND_SUG on UI.ArticleID = KB_AND_SUG.ArticleID
		
	--WHERE VRS.Operating_System_Name_and0 like '%Server%'
	AND Col.CollectionID = @Collection
	AND DateCreated BETWEEN @StartDate AND @EndDate
	--AND CategoryInstanceName = 'Security Updates' -- Only Security Updates
	
	ORDER BY DateCreated DESC