Custom Software History report

[harringg]

I've got a software history report (when software was installed or removed for a given PC).

The result of the report for a given record is (in this case Google Chrome 38.0.2125.111 was installed, 10/28/2014)

The GUI (web page) for History for that asset is attached and shows the green plus on 10/28/2014.
In the GUI, it shows a date behind the software name, in this case, 03/05/2014, but that value is the same for two different SQL records. What is that date in terms of record keeping? Is it a major upgrade change date (v20 to v30)?

I notice too that the most current version is listed as tblSoftware.softwareVersion in the report, may or may not be useful, that's why it's still in my report.

Some other software titles only have a value in the tblSoftwareHist.LastChanged, but nothing under tblSoftwareHist.Installdate or tblSoftware.Installdate.

What would the proper way to modify the report below to give me (or something as closely accurate to):
PC Name
Current Software Title:
Current Software Version:
Software Title: Installed or Removed (1 or 2)
Software Title Date Installed (or Removed):


MYPC01 Windows
[tblSoftwareHist.softwareVersion]38.0.2125.111
[tblSoftwareHist.Installdate]03/05/2014
[tblSoftwareHist.Lastchanged]10/28/2014 23:21:31
[tblSoftwareHist.Action]Installed
[tblSoftwareUni.softwareName]Google Chrome
[tblSoftwareUni.SoftwarePublisher]Google Inc.
[tblSoftware.softwareVersion] As softwareVersion1]40.0.2214.94
[tblSoftware.Installdate] As Installdate1]03/05/2014

 SELECT TOP 1000000 tblassets.assetid,
                   tblassets.assetname,
                   tsysassettypes.assettypename,
                   tsysassettypes.assettypeicon10 AS icon,
                   tblsoftwarehist.softwareversion,
                   tblsoftwarehist.installdate,
                   tblsoftwarehist.lastchanged,
                   CASE
                     WHEN tblsoftwarehist.action = '1' THEN 'Installed'
                     WHEN tblsoftwarehist.action = '2' THEN 'Removed'
                   END                            AS Action,
                   tblsoftwareuni.softwarename,
                   tblsoftwareuni.softwarepublisher,
                   tblsoftware.softwareversion    AS softwareVersion1,
                   tblsoftware.installdate        AS Installdate1
FROM   tblassets
       INNER JOIN tblsoftwarehist
               ON tblassets.assetid = tblsoftwarehist.assetid
       INNER JOIN tsysassettypes
               ON tsysassettypes.assettype = tblassets.assettype
       INNER JOIN tblsoftwareuni
               ON tblsoftwareuni.softid = tblsoftwarehist.softid
       INNER JOIN tblsoftware
               ON tblassets.assetid = tblsoftware.assetid
                  AND tblsoftwareuni.softid = tblsoftware.softid
WHERE  tblassets.assetname = 'MYPC01' 

[Daniel_B]

One remark to the last post: This report didn't properly run on SQL Compact for some reason. We could only test it successfully on SQL server Express.

[Daniel_B · Accepted solution]

tblSoftware.InstallDate and tblSoftwareHist.InstallDate are always the same for the same AssetID and softID. In the history table, only the Lastchanged column will be updated to the date when a change in the list of installed software for an asset was detected. tblSoftwareHist.Lastchanged refers to the date when the computer was scanned and the change was noticed by Lansweeper.

The following report will list all installed software, their install date and the date of the last change listed in tblSoftwareHist:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion As [Current version],
  tblSoftware.Installdate As [Software Install date],
  Case When tLastChange.Action = '1' Then 'Installed'
    When tLastChange.Action = '2' Then 'Removed' End As [Last action],
  tLastChange.SWVersionChange,
  tLastChange.[Last change] As [Action date]
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Left Join (Select tLastSWChange.[Last change],
    tblSoftwareHist.Action,
    tblSoftwareHist.softid,
    tblSoftwareHist.softwareVersion As SWVersionChange,
    tblSoftwareHist.AssetID
  From (Select Max(tblSoftwareHist.Lastchanged) As [Last change],
      tblSoftwareHist.AssetID,
      tblSoftwareHist.softid
    From tblSoftwareHist
    Group By tblSoftwareHist.AssetID,
      tblSoftwareHist.softid) tLastSWChange
    Inner Join tblSoftwareHist On tLastSWChange.AssetID =
      tblSoftwareHist.AssetID And tLastSWChange.softid = tblSoftwareHist.softid
      And tLastSWChange.[Last change] = tblSoftwareHist.Lastchanged) tLastChange
    On tblAssets.AssetID = tLastChange.AssetID And tblSoftware.softID =
    tLastChange.softid
Order By tblAssets.AssetName,
  tblSoftwareUni.softwareName