Merge two reports

[robd]

Hello,

I've got lansweeper scanning for a file which seems to work great but I'd like a report that:

Tells if a certain piece of software is installed and
If the file I'm scanning for does Not exist.

Basically you can have the software installed but without the file it breaks stuff.

I think i'm trying to combine these but just not getting the hang of it:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tsysOS.OSname,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblFileVersions.AssetID
  From tblFileVersions
  Where
    tblFileVersions.FilePathfull Like
    'C:\ProgramData\Progammy\File.dll' And
    tblFileVersions.Found = 'False') And tblAssetCustom.State = 1
Order By tblAssets.AssetName



Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon
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
Where tblSoftwareUni.softwareName Like 'programmy' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version

[robd]

Thank you both, really appreciate the reply's and I'll read the tutorial to try and be able to sort this myself in the future.

[RCorbeil · Accepted solution]

Try something like this as a launchpad:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblFileVersions.FilePathfull,
  CASE tblFileVersions.Found
    WHEN 0 THEN 'Absent'
    WHEN 1 THEN 'Present'
    ELSE 'Undefined'
  END As FileFound
FROM 
  tblAssets
  INNER JOIN tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  LEFT JOIN tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName Like '%programmy%'
  AND tblFileVersions.FilePathfull = 'C:\ProgramData\Progammy\File.dll'

That should produce a list of all machines with programmy installed and indicate whether or not C:\ProgramData\Progammy\File.dll is also present on the machine.

[David_G]

I have merged the two reports into one, resulting in assets where the software 'programmy' is installed but where the file was not found by Lansweeper. In your first query, I have changed the parameter False to True, as you were using Not in and False, resulting in assets that do have the file installed. 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 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon
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
Where tblAssets.AssetID Not In (Select tblFileVersions.AssetID
  From tblFileVersions
  Where tblFileVersions.FilePathfull Like 'C:\ProgramData\Progammy\File.dll' And
    tblFileVersions.Found = 'True') And tblSoftwareUni.softwareName Like
  'programmy' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version

[robd]

Anyone???