Report on MS office installed during last 360 days

[amagner]

Hi all, we have an EA with MS for MSOffice and need to "true up" our license count every year with MS. I am trying to build a report on MS Office installations during the past 360 days so I can get a delta from last year's count. I am stumped as the Installdate field is NOT a date field in SQL terms and so has to be converted to date in order to perform date conditions on it. Below is my code but it is not working. Can aybody help with the Installdate conversion/conditional statements?

Thanks

Select Distinct Top 1000000 dbo.tblSoftware.softwareName As Software,
dbo.tblSoftware.softwareVersion As Version, Count(dbo.tblSoftware.SoftwareID)
As Total
From dbo.tblSoftware Inner Join
dbo.tblComputers On dbo.tblSoftware.ComputerName =
dbo.tblComputers.Computername Inner Join
dbo.web40ActiveComputers On dbo.tblComputers.Computername =
dbo.web40ActiveComputers.Computername
Where dbo.tblSoftware.SoftwarePublisher Like '%Microsoft Office%' And
dbo.tblSoftware.Installdate =
'= Cast(dbo.tblSoftware.Installdate As SmallDateTime) >= DateAdd(d, -360, GetDate())'
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion
Order By Count(dbo.tblSoftware.SoftwareID) Desc, dbo.tblSoftware.softwareName

[JasonG]

Most properly setup installation packages will set the installation date (IIRC you can't even disable it when using MSI).

You can detemine the date of installation for most software packages by looking at HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\PACKAGE_GUID\InstallDate. It is a string value of YYYYMMDD. Microsoft Office GUIDs are formalized, and explained here.

You can also use WMI and query for Win32_Product Try 'wmic product get installdate, name | grep --ignore-case office', or 'Get-WmiObject -query "SELECT Name, InstallDate FROM Win32_Product WHERE Name LIKE `"%office%`""' in PowerShell.

As far as getting it in LAN Sweeper, consider the following:

SELECT
		tblComputers.Computer AS [Computer]
		,tblSoftware.SoftwareName AS [Name]
		,CASE ISDATE(tblSoftware.InstallDate) WHEN '1' THEN CAST(tblSoftware.InstallDate AS SMALLDATETIME) END AS [InstallDate]
	FROM tblComputers
	RIGHT JOIN tblSoftware
		ON tblComputers.ComputerName = tblSoftware.ComputerName
	WHERE
		tblSoftware.SoftwareName LIKE '%microsoft%office%'
		AND  DATEDIFF(YEAR, CASE ISDATE(tblSoftware.InstallDate) WHEN '1' THEN CAST(tblSoftware.InstallDate AS SMALLDATETIME) END, GETDATE()) < 1

Here we use a CASE to check ISDATE on the date string and see if it is valid. If not, leave the field as NULL.

[amagner]

After further investigation, it looks like MS Office 2007 and 2010 installers don't populate that registry entry anyway. Those are the versions we're interested in. Thanks anyway to all who even gave this any thought.

[Hemoco · Accepted solution]

The installdate is not a sql date field because the format in the registry is not "uniform", vendors can put in the field whatever they like.
DD/MM/YYYY, MM/YYYY, ...