Highlight missing software on Win, Mac and Linux

[lanny]

I have a report that highlights the app's status on Windows. However, I struggle to expand this report to Mac and Linux.

My current Windows report

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Adobe,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Notepad,
  tblAssets.Lastseen,
  Case
    When soft01.AssetID Is Null Or soft02.AssetID Is Null Then '#ffcccc'
  End As backgroundcolor,
  tblAssetCustom.State
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Adobe%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen > DateAdd(day, -7, GetDate())

I used https://www.lansweeper.com/resources/report/software/installed-software-by-computer/ as a base. However, something is missing. It does not identify the required software on Macs and Linux. It includes all devices but is accurate only for Windows devices

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Adobe,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Notepad,
  tblAssets.Lastseen,
  Case
    When soft01.AssetID Is Null Or soft02.AssetID Is Null Then '#ffcccc'
  End As backgroundcolor,
  tblAssetCustom.State
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 tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Adobe%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen > DateAdd(day, -7, GetDate())
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblMacOSInfo.SystemVersion As OS,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Adobe,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Notepad,
  tblAssets.Lastseen,
  Case
    When soft01.AssetID Is Null Or soft02.AssetID Is Null Then '#ffcccc'
  End As backgroundcolor,
  tblAssetCustom.State
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 tblMacApplications On
      tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid
  Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Adobe%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen > DateAdd(day, -7, GetDate())
Union
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblLinuxSystem.OSRelease As OS,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Adobe,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Notepad,
  tblAssets.Lastseen,
  Case
    When soft01.AssetID Is Null Or soft02.AssetID Is Null Then '#ffcccc'
  End As backgroundcolor,
  tblAssetCustom.State
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
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Adobe%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen > DateAdd(day, -7, GetDate())

[KevinA-REJIS]

I think the issue is with the soft01/02 Left Joins for Mac and Linux, they're not searching their respective software tables just the Windows one.

Try these:

Mac

Select tblMacApplications.AssetID
    From tblMacApplications
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%'

Linux

Select tblLinuxSoftware.AssetID
    From tblLinuxSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblLinuxSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad%'

[lanny]

Hi Kevin,
I tried it. Now I am getting "Invalid column name 'softID'. Invalid column name 'softID'. Invalid column name 'softID'." error at saving it and it does not mention the line. 

[KevinA-REJIS · Accepted solution]

Apologies @lanny I was out of the office last week.

The Linux portion should actually be:

Select tblLinuxSoftware.AssetID
    From tblLinuxSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblLinuxSoftware.SoftwareUniID
    Where tblSoftwareUni.softwareName Like '%Notepad%'

[lanny]

@KevinA-REJIS ,it worked, thank you very much!