Linux Missing Software

[thomasmrazek]

Hello, I am trying to create a report that shows all Linux servers that DO NOT have a piece of software. Attached is what i found but this shows all software on all machines not named the actual software. So instead of showing each server 1 time, it shows each server many times for each piece of software that is not named the software that is missing. 

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblLinuxSystem.OSRelease As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblLinuxSoftware.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 tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
      tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblLinuxSoftware.AssetID
    From tblLinuxSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblLinuxSoftware.SoftwareUniID
    Where tblSoftwareUni.softwareName Like '%managesoft%') And
  tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Software

There is a report for windows servers but there is not one for linux. Thank you for the help. 

[thomasmrazek]

Thank you David. This is exactly what i was looking for. Worked perfectly.

[DavidPK · Accepted solution]

Hi,

We've created a sample report that returns all Linux servers that DO NOT have a piece of software. Instructions for adding this report to your Lansweeper installation can be found 

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: https://www.w3schools.com/sql/
• 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: https://www.lansweeper.com/knowledgebase/accessing-the-lansweeper-database-documentation/
• Checking out our report library: https://www.lansweeper.com/report/

SELECT
   tblAssets.AssetID,
   tblAssets.AssetName,
   tblAssets.Domain,
   tsysAssetTypes.AssetTypename AS AssetType,
   tblAssets.Lastseen,
   tblLinuxSystem.OSRelease AS OS,
   tblAssetCustom.Manufacturer,
   tblAssetCustom.Model
FROM
   tblAssets
   INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
   INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
   INNER JOIN tblState ON tblState.State = tblAssetCustom.State
   INNER JOIN tblLinuxSystem ON tblAssets.AssetID = tblLinuxSystem.AssetID
WHERE
   tblAssets.AssetID NOT IN (
       SELECT
           tblLinuxSoftware.AssetID
       FROM
           tblLinuxSoftware
           INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
       WHERE
           tblSoftwareUni.softwareName LIKE '%managesoft%'
   )
   AND tblState.Statename = 'Active'
ORDER BY
   tblAssets.Domain,
   tblAssets.AssetName;