→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
amagner
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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, ...

View solution in original post

3 REPLIES 3
JasonG
Engaged Sweeper
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
Engaged Sweeper II
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
Lansweeper Alumni
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, ...