Whether or not software is installed AND what version AND is related service running

[joe_user]

I currently have three reports that I can combine in Excel using vlookup, but I'd like to know how to get the information in one Lansweeper report.

My report below shows whether or not the Umbrella client is installed on our laptops. I'd also like the report to show the version of the Umbrella software and if the related service is running. The service is named "Umbrella Roaming Client."

Thank you!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
(tblSoftwareUni.softwareName Like '%Umbrella%' Or
tblSoftwareUni.softwareName Like '%Umbrella%')) Then 'YES' Else 'NO'
End As UmbrellaFound
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery On tblPortableBattery.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1

[Esben_D]

The following should work. I replaced the subquery of the software found with simply normal fields and added the service and state of the service.

The database is well documented, so if you need to find things I'd recommend taking a look at the database documentation first.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblServicesUni.Caption As Service,
  tblServiceState.State,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPortableBattery On tblPortableBattery.AssetID =
    tblAssets.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
  Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
  Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
    tblServices.ServiceuniqueID
Where tblSoftwareUni.softwareName Like '%Umbrella%' And
  tblServicesUni.Caption Like '%Umbrella Roaming Client%' And
  tblAssetCustom.State = 1