SMS 2003 - Reports - Computer Model Reports (Counts and Details)
Recently I was asked to provide some SMS web reports for upcoming budget planning sessions. Specifically, managers wanted computer model reports for machine refresh expenditures. I created a few reports, but these four have proven very useful and were well-received. Perhaps some of you may find these reports useful. I have attached the MOF files for importing. There are four reports supplied here. I hope others find these useful.
Count all Computer Models:
SELECT Model0, COUNT(Model0) AS Expr1
FROM v_GS_COMPUTER_SYSTEM
GROUP BY Model0
ORDER BY Model0
Count Computer Models by Selection:
SELECT Model0, COUNT(Model0) AS Expr1
FROM v_GS_COMPUTER_SYSTEM
WHERE Model0 = @variable
GROUP BY Model0
Prompt:
SELECT DISTINCT Model0 FROM v_GS_COMPUTER_SYSTEM
ORDER BY Model0
Count Computer Models by Selection (Detail View):
SELECT v_R_System.Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
v_R_System.Operating_System_Name_and0
FROM v_R_System INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
WHERE (v_GS_COMPUTER_SYSTEM.Model0 = @variable)
ORDER BY v_R_System.Name0
Prompt:
SELECT DISTINCT Model0 FROM v_GS_COMPUTER_SYSTEM
ORDER BY Model0
Count Computer Models by Site (AD Boundaries Only):
SELECT v_SiteBoundary_ADSite.SiteCode, v_GS_COMPUTER_SYSTEM.Model0, COUNT(DISTINCT v_GS_COMPUTER_SYSTEM.ResourceID) AS Count
FROM v_GS_COMPUTER_SYSTEM INNER JOIN
v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
v_SiteBoundary_ADSite ON v_R_System.AD_Site_Name0 = v_SiteBoundary_ADSite.ADSiteName
WHERE (v_SiteBoundary_ADSite.SiteCode LIKE @variable)
GROUP BY v_SiteBoundary_ADSite.SiteCode, v_GS_COMPUTER_SYSTEM.Model0
Prompt:
SELECT SiteCode, v_Site.SiteName FROM v_Site