Report Request - List out of date software but include date it was installed/last updated

[Tythesly]

This is the code I use to list assets that have an out of date version of 7-ZIP.

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblassets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblassets.Lasttried,
  tblSoftware.Lastchanged
From tblassets
  Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblassets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblassets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblassets.OScode
Where tblSoftwareUni.softwareName Like '7-ZIP%' And
  tblSoftware.softwareVersion Not Like '19.00%' And tblState.Statename =
  'Active'
Order By Version,
  tblassets.Lastseen Desc,
  tblassets.AssetName

I wanted to add a field that tells me when it was installed and the last time the program was updated. I went and tried adding Installdate and Lastchanged from tblSoftware but that didn't seem give me correct values. Then I tried tblSoftwareHist Installdate and Lastchanged which may have given correct values. (I don't know the difference between the two tables.) The only problem is that if I put either of those in it adds a TON of entries instead of just showing me computers with an out of date 7-ZIP. It goes from 340 to 194814.

Any ideas?

[Tythesly]

Hey Thanks for the help! I really appreciate it!

[RCorbeil · Accepted solution]

I went and tried adding Installdate and Lastchanged from tblSoftware but that didn't seem give me correct values. Then I tried tblSoftwareHist Installdate and Lastchanged which may have given correct values. (I don't know the difference between the two tables.)

tblSoftware is the list of currently-installed software.
tblSoftwareHist, as the name suggests, is a history of changes: software installed and removed since the asset was initially scanned.

According to the database documentation, InstallDate is the date the software was installed and LastChanged is the date the data table was last updated. LastChanged won't likely be be exactly the date the software was installed, but it shouldn't be more than a couple of days out, depending on how frequently your LANSweeper scans for software changes. Those values from tblSoftware should reflect when the currently-installed version was installed and the table entry updated.

From my inventory, it's not uncommon to have no InstallDate value recorded, so I'll use IsNull(InstallDate, LastChanged) below to fall back on LastChanged where there is no InstallDate value.

Select Top 1000000
  tblassets.AssetID,
  tblassets.AssetName,
  tblassets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.InstallDate,
  tblSoftware.LastChanged,

  ( SELECT                                                  -- if InstallDate is null, use LastChanged as a better-than-nothing alternative
      Max(IsNull(tblSoftwareHist.InstallDate, tblSoftwareHist.LastChanged))
    FROM
      tblSoftwareHist
    WHERE
      tblSoftwareHist.AssetID = tblAssets.AssetID           -- for this computer
      AND tblSoftwareHist.SoftID = tblSoftware.SoftID       -- and this piece of software
      AND tblSoftwareHist.Action = 1                        -- installed
                                                            -- prior to the current install
      AND IsNull(tblSoftwareHist.InstallDate, tblSoftwareHist.LastChanged) < IsNull(tblSoftware.InstallDate, tblSoftware.LastChanged)
  ) AS PreviousInstall,

  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblassets.Lasttried,
  tblSoftware.Lastchanged
From
  tblassets
  Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblassets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblassets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblassets.OScode
Where
  tblSoftwareUni.softwareName Like '7-Zip%'
  And tblSoftware.softwareVersion Not Like '19.00%'
  And tblState.Statename = 'Active'
Order By
  Version,
  tblassets.Lastseen Desc,
  tblassets.AssetName