Notification

Icon
Error

Report Request - List out of date software but include date it was installed/last updated - Can't figure it out.

Posted: Thursday, May 6, 2021 8:17:45 PM(UTC)
Tythesly

Tyler M.

Member Original PosterPosts: 31
1
Like
This issue has been solved! Click here to view the solution
This is the code I use to list assets that have an out of date version of 7-ZIP.

Code:
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?
RC62N
#1RC62N Member Posts: 528  
posted: 6/16/2021 2:26:35 PM(UTC)
Quote:
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.

Code:
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
Tythesly
#2Tythesly Member Original PosterPosts: 31  
posted: 6/16/2021 3:11:09 PM(UTC)
Hey Thanks for the help! I really appreciate it!

Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
by  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)