Software Install Review via the Add Remove Table
Software Install Review for Add Remove Table
I was reading Joe Funk’s Blog Post SMS Reports - Software Install Review.
I noticed he was using the v_GS_INSTALLED_SOFTWARE table which is included with Asset Intelligence features or ConfigMgr 2007. For those of us who do not have access to that information or for those whose information is not populating. I made a couple of changes so these reports can be run against the v_HS_ADD_REMOVE_PROGRAMS and v_GS_ADD_REMOVE_PROGRAMS tables
As always be aware of line wrap
Step 1 – Installdate0 Field
First thing I noticed is that in the v_GS_ADD_REMOVE_PROGRAMS table is that the values of InstallDate0 are the Wild West, from null to every format possible to convey some value.
Take a look at your own with this query:
SELECT DISTINCT InstallDate0
FROM dbo.v_GS_ADD_REMOVE_PROGRAMS
Example output:
20071011
4/11/2008
Tue Sep 23 08:09:58 PDT 2008
20080108
20060510
2006-10-30 14:30:53
12-5-2006
1/7/2008
Step 2 – Filter out invalid dates
Used the following SQL code to only work with those with valid date times
((CASE ISDATE(INST.InstallDate0) WHEN 1 THEN CAST(INST.InstallDate0 AS DateTime) ELSE NULL END)
Step 3 – Modify the Queries
Software Install review - Count, per application, of every install over past X days
'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'
Declare @days INT;
Set @days = 4;
SELECT INST.DisplayName0 AS Product, COUNT(*) AS Count, @days AS 'Scope of software install review (days)'
FROM dbo.v_GS_ADD_REMOVE_PROGRAMS AS INST INNER JOIN
dbo.v_R_System AS SYS ON SYS.ResourceID = INST.ResourceID
WHERE ((CASE ISDATE(INST.InstallDate0) WHEN 1 THEN CAST(INST.InstallDate0 AS DateTime) ELSE NULL END) >= DATEADD(dd, - CONVERT(INT, @days),
CURRENT_TIMESTAMP))
GROUP BY INST.DisplayName0
ORDER BY COUNT DESC, INST.DisplayName0
'Historical - Uninstalled/Reinstalled - This report contains records of software that was installed within the review period but was since uninstalled or reinstalled'
Declare @days INT;
Set @days = 4;
SELECT INSTHist.DisplayName0 AS Product, COUNT(*) AS Count, @days AS 'Scope of software install review (days)'
FROM dbo.v_HS_ADD_REMOVE_PROGRAMS AS INSTHist INNER JOIN
dbo.v_R_System AS SYS ON SYS.ResourceID = INSTHist.ResourceID
WHERE ((CASE ISDATE(INSTHist.InstallDate0) WHEN 1 THEN CAST(INSTHist.InstallDate0 AS DateTime) ELSE NULL END) >= DATEADD(dd, - CONVERT(INT, @days),
CURRENT_TIMESTAMP))
GROUP BY INSTHist.DisplayName0
ORDER BY COUNT DESC, INSTHist.DisplayName0
Next steps are to work on the values I ignored as well as values with a NULL install date.