SMS Reports - Software Install Review

2008-12-18T16:04:00Z

Recently I had a request to create a 'software install review' report that could minimally determine what specific software products had been installed, and where they had been installed, over the past 24 hours. Mainly the request was made in order to audit unsupported or unauthorized software installs.

Initially the efforts were focused on simply querying v_GS_INSTALLED_SOFTWARE for the value of v_GS_INSTALLED_SOFTWARE.InstallDate0 and returning only the rows in which that value fell within the past day.

One thing that is important to realize about v_GS_INSTALLED_SOFTWARE is that (as with all GS prefixed views) is that it only contains 'current' data. In terms of making the report as valid as possible, using only the GS view would not necessarily report about all the installs that occurred for instance if it had been uninstalled since. It would only contain records for the software only if at the last point in time that a Hardware Inventory occurred on the client the software still was installed. After thinking through the motive behind the request itself it was concluded that possibly a JOIN or UNION would be applicable on the v_HS_INSTALLED_SOFTWARE, however we did not want to cram them together, nor did we want two entirely separate report links (though in essence, they are in fact two separate reports one one report page).

What we ended up with was two basic reports:

(Custom report) Software Install review - Count, per application, of every install over past X days

SELECT

'Currently Installed - This report contains only records for software that was installed (and remains installed) within the review period still installed when the SMS client last checked' AS Report

SELECT

       INST.ProductName0 AS Product,
       COUNT(*) AS Count,
       @days AS 'Scope of software install review (days)'

FROM

v_GS_INSTALLED_SOFTWARE INST

JOIN

v_R_System SYS ON SYS.ResourceID = INST.ResourceID

WHERE

INST.InstallDate0 >= DATEADD(dd,-(CONVERT(INT, @days)),CURRENT_TIMESTAMP)

GROUP BY

INST.ProductName0

ORDER BY

COUNT DESC,

INST.Productname0

SELECT

'Historical - Uninstalled/Reinstalled - This report contains records of software that was installed within the review period but was since uninstalled or reinstalled' AS Report

SELECT

       INSTHist.ProductName0 AS Product,
       COUNT(*) AS Count,
       @days AS 'Scope of software install review (days)'

FROM

v_HS_INSTALLED_SOFTWARE INSTHist

JOIN

v_R_System SYS ON SYS.ResourceID = INSTHist.ResourceID

WHERE

INSTHist.InstallDate0 >= DATEADD(dd,-(CONVERT(INT, @days)),CURRENT_TIMESTAMP)

GROUP BY

INSTHist.ProductName0

ORDER BY

COUNT DESC,

INSTHist.Productname0

With a prompt for number of 'days', no query.

That report is in turn linked to the second report (via columns 1 and 3):

(Custom report) Software Install review - All software installs over the past X days for a specific application

SELECT

'Currently Installed - This report contains only records for software that was still installed when the SMS client last checked' as Report

SELECT

SYS.Name0 AS System,

INST.Publisher0 AS Vendor,

INST.ProductName0 AS Software,

INST.ProductVersion0 AS 'Software Version',

INST.InstallSource0 AS 'Install source location',

INST.InstallDate0 AS 'Date Installed'

FROM

v_GS_INSTALLED_SOFTWARE INST

JOIN

v_R_System SYS on SYS.ResourceID = INST.ResourceID

WHERE

INST.InstallDate0 >= dateadd(dd,-(CONVERT(int, @days)),current_timestamp) and

INST.ProductName0 = @software

ORDER BY

SYS.Name0

SELECT

'Historical - Uninstalled/Reinstalled - This report contains records of software that was installed within the review period but was since been uninstalled (and possibly reinstalled) as of the last point the SMS client checked' as Report

SELECT

SYS.Name0 as System,

INSTHist.Publisher0 as Vendor,

INSTHist.ProductName0 as Software,

INSTHist.ProductVersion0 as 'Software Version',

INSTHist.InstallSource0 as 'Install source location',

INSTHist.InstallDate0 as 'Date Installed'

FROM

v_HS_INSTALLED_SOFTWARE INSTHist

JOIN

v_R_System SYS on SYS.ResourceID = INSTHist.ResourceID

WHERE

INSTHist.InstallDate0 >= dateadd(dd,-(CONVERT(int, @days)),current_timestamp) and

INSTHist.ProductName0 = @software

ORDER BY

SYS.Name0

There are two prompts to the second report. Again there is one for the 'days' (with no SQL) and additionally one for the product name. As you can see in the above query, we used 'software' as the variable name that the product name maps to. You can use the below SQL to generate a drop down option for the product name (software variable).

BEGIN

if (@__filterwildcard = '')

SELECT DISTINCT

INST.ProductName0

FROM

v_GS_INSTALLED_SOFTWARE INST

ORDER BY

INST.ProductName0

ELSE

SELECT DISTINCT

INST.ProductName0

FROM

v_GS_INSTALLED_SOFTWARE INST

WHERE

INST.ProductName0 like @__filterwildcard

ORDER BY

INST.ProductName0

END

Now it is important to note that any software that the Windows Installer is not aware of will not be included in the report. For example Oracle Server or Client software that is installed via the "Universal Installer" (which is itself a stand alone Java based installer) does not involve the Windows Installer and hence will not show up on the report.

-joe-

Welcome!

2008-12-11T20:30:54Z

Welcome to my blog.

This is my first post and I plan to make some updates very soon.

Thanks for stopping by.

Joe Funk at myITforum.com

SMS Reports - Software Install Review

Welcome!