08-29-2024 01:04 AM
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())
Solved! Go to Solution.
09-09-2024 10:21 PM
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%'
08-30-2024 06:23 PM - edited 08-30-2024 06:24 PM
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%'
08-31-2024 05:46 AM
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.
09-09-2024 10:21 PM
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%'
09-10-2024 12:31 AM
@KevinA-REJIS ,it worked, thank you very much!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now