SMS 2003 - Reports - Software Inventory of a Specific Product for a Specific Collection
The company for which I work has many various contracts with separate cost centers, few of which are affiliated with one another. Because of this, I am frequently called upon to create SMS reports which are segregated by cost center. During our initial setup of SMS we thought carefully about this particular aspect of our company and adopted a naming convention we had partially used in the past. With this naming convention, we are able to separate out all machines by cost center, and thus have them populating their own SMS collections.
As with any company practicing some sort of asset management, we conduct software audits in order to "true up" with our licensing agreements. This was the first year since we've had SMS 2003 installed that we've utilized the reporting functions of SMS to produce these reports. In the past, the company has used purchasing records entered into Service Center for this purpose. Needless to say, the SMS reports were far more accurate and more easily manipulated to provide the exact information our asset management department needed.
This particular report has two variables which are part of the selection criteria: 1) Software Product and 2) Collection ID. This particular report will produce pertinent information about the software product selected for the collection selected. I've included the SQL query, prompt queries and a zipped MOF file in the case you want to import it.
Report Query:
SELECT SYS.Netbios_Name0, SYS.AD_Site_Name0, SP.CompanyName, SP.ProductName, SF.FileName, SF.FileSize, SF.FileVersion, SF.FilePath
FROM v_R_System SYS INNER JOIN
v_GS_SoftwareProduct SP ON SYS.ResourceID = SP.ResourceID INNER JOIN
v_GS_SoftwareFile SF ON SP.ProductID = SF.ProductId AND SP.ResourceID = SF.ResourceID INNER JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
WHERE (SP.ProductName LIKE @productname ) AND (FCM.CollectionID = @collection)
ORDER BY SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SF.FileName, SF.FileVersion, SF.FilePath
Prompt Query for Variable 'productname':
begin
if (@__filterwildcard = '')
select distinct ProductName from v_GS_SoftwareProduct order by ProductName
else
select distinct ProductName from v_GS_SoftwareProduct
WHERE ProductName like @__filterwildcard
order by ProductName
end
Prompt Query for Variable 'collection':
SELECT DISTINCT CollectionID, Name
FROM v_Collection
ORDER BY Name