cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper

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'

 

1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
Daniel_B
Lansweeper Alumni
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
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now