Beyond SCCM build in reports

In large organization, we will hit the limit of the build in SCCM reports quite often. Then only the SQL native query came to play. Here is some SQL that I was using lately.
 

This will produce a count off all Software Products inventoried:

SELECT
 dbo.v_GS_SoftwareProduct.CompanyName,
 dbo.v_GS_SoftwareProduct.ProductName,
 dbo.v_GS_SoftwareProduct.ProductVersion,
COUNT
 (DISTINCT dbo.v_GS_SYSTEM.ResourceID)
AS
 Expr1
FROM
 dbo.v_GS_SoftwareProduct
INNER JOIN
 dbo.v_GS_SYSTEM
ON
 dbo.v_GS_SoftwareProduct.ResourceID = dbo.v_GS_SYSTEM.ResourceID
GROUP BY
 dbo.v_GS_SoftwareProduct.ProductVersion,
 dbo.v_GS_SoftwareProduct.CompanyName,
 dbo.v_GS_SoftwareProduct.ProductName


This will list a count of software products that we are looking for:

 
SELECT
 dbo.v_GS_SoftwareProduct.CompanyName,
 dbo.v_GS_SoftwareProduct.ProductName,
 dbo.v_GS_SoftwareProduct.ProductVersion,
COUNT
 (DISTINCT dbo.v_GS_SYSTEM.ResourceID)
AS
 Expr1
FROM
 dbo.v_GS_SoftwareProduct
INNER JOIN
 dbo.v_GS_SYSTEM
ON
 dbo.v_GS_SoftwareProduct.ResourceID = dbo.v_GS_SYSTEM.ResourceID
GROUP BY
 dbo.v_GS_SoftwareProduct.ProductVersion,
 dbo.v_GS_SoftwareProduct.ProductName,
 dbo.v_GS_SoftwareProduct.CompanyName
HAVING
 (dbo.v_GS_SoftwareProduct.CompanyName = 'UltraVNC')
 or (dbo.v_GS_SoftwareProduct.ProductName = 'Vuze')
 or (dbo.v_GS_SoftwareProduct.ProductName = 'Vuze Toolbar')
 or (dbo.v_GS_SoftwareProduct.ProductName = 'Warcraft III')
 

Produce a count of all computer models:

SELECT
 Model0,
COUNT
 (Model0)
AS
 Expr1
FROM
 v_GS_COMPUTER_SYSTEM
GROUP BY
 Model0
ORDER BY
Mode0
 
 

 

 
Comments