Software Installed and Last Patch Detected Report

[hlee]

Need to merge two reports and I'm having difficulty to combine both to one report. One is Software Inventory Report with 3 different software's, and the other is when last time Microsoft patch was installed. Both has asset name, and Username as common shared fields, and we use Assetname to search / filter out certain computer. Thanks in advance!

Software Report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblADusers.email,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblAssets.Lastseen,
tblADusers.OU
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where (tblSoftwareUni.softwareName Like 'Trend Micro OfficeScan%') Or
(tblSoftwareUni.softwareName Like 'Pulse Secure 5.1%') Or
(tblSoftwareUni.softwareName Like 'Check Point Endpoint%' And
tblAssetCustom.State = 1)
Order By tblAssets.AssetName,
software,
version
-----------------------------------------------------------------------------------
Last Microsoft Patched Detected:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.OSname As [Operating System],
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
Max(tblQuickFixEngineering.Lastchanged) As LastPatchDetected
From tblAssets
Inner Join tblQuickFixEngineering On tblAssets.AssetID =
tblQuickFixEngineering.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblAssetCustom.State = 1
Group By tsysOS.Image,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.OSname,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen
Order By LastPatchDetected

[David_G]

We have merged the two reports you have provided us with into one report below. Instructions for adding this report to your Lansweeper installation can be found here.

If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.

• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysOS.OSname As [Operating System],
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  Max(tblQuickFixEngineering.Lastchanged) As LastPatchDetected,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblADusers.OU
From tblAssets
  Inner Join tblQuickFixEngineering On tblAssets.AssetID =
    tblQuickFixEngineering.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssetCustom.State = 1
Group By tsysOS.Image,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysOS.OSname,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblADusers.OU
Having (tblSoftwareUni.softwareName Like 'Trend Micro OfficeScan%') Or
  (tblSoftwareUni.softwareName Like 'Pulse Secure 5.1%') Or
  (tblSoftwareUni.softwareName Like 'Check Point Endpoint%')
Order By LastPatchDetected