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
|
|