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 to my blog.
This is my first post and I plan to make some updates very soon.
Thanks for stopping by.