History Report

[harringg]

Using the History tab on the main page of a PC, one of the things it shows is HotFix information, ie. Security Update KB2813170

Is there a report that will let me see if a PC has a given HotFix applied, based on data from that table? Which table is it stored in?

Or it could be customized to report on when a given piece of software was added or removed (I'm assuming the plus/minus icon is shown based on a value in the underlying table).

[harringg]

Great! Thanks.

[harringg]

Thanks for the Hotfix link.

This is what I was looking for however, the Software History. I found it and built this report from scratch. We are migrating from Symantec Endpoint Protection v11 to SEP v12. This report is showing me when SEP client was installed and which PC's in a given departments have been deployed. I can get this data in the SEP Manager, but this allows for a quick summary report I can put into Excel for management.

Is there some SQL code that will return the tblsoftwarehist.installdate, which is shown as 04/08/2013 00:00:00, as 04/08/2013 in the Web Interface? When I dump the data to Excel, I can quickly convert the field to the proper date format, but would like to know to have in the LS report preformated.

 SELECT TOP 1000000 tblassets.assetid,
                   tblassets.assetname,
                   tsysassettypes.assettypeicon10 AS icon,
                   tblsoftwareuni.softwarename,
                   tblsoftwarehist.softwareversion,
                   tblassetcustom.department,
                   tblsoftwarehist.installdate
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 tblassetcustom
               ON tblassets.assetid = tblassetcustom.assetid
WHERE  tblsoftwarehist.softwareversion = '12.1.2015.2015'
ORDER  BY tblsoftwarehist.installdate  

P.S. tblSoftwareHist.Action has a value of 1 for Added and 2 for Removed if you are looking to use that as a filter in your own reports.

[Hemoco]

harringg wrote:
Is there some SQL code that will return the tblsoftwarehist.installdate, which is shown as 04/08/2013 00:00:00, as 04/08/2013 in the Web Interface?

Try:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblSoftwareUni.softwareName,
tblSoftwareHist.softwareVersion,
tblAssetCustom.Department,
Convert(nvarchar,tblSoftwareHist.Installdate,101) As InstallDate
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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblSoftwareHist.softwareVersion = '12.1.2015.2015'
Order By Convert(nvarchar,tblSoftwareHist.Installdate,101)

[Hemoco · Accepted solution]

Updates are stored in tblQuickFixEngineering. A sample report was posted here: http://lansweeper.com/forum/yaf_postst7344_Searching-for-an-installed-KB.aspx
Note that a hot fix overview can also be found under Config\Windows\Quickfix on asset webpages.