SCCM service Start Mode and executable version

[AnthelioHealth]

I have been working on a report that would return all assets scanned by specific servers that either do or do not have SCCM service running. If the service is running, show the StartMode of the service and also show the version of the executable (ccmexec.exe). So far I can only get returns for devices where the service is running. Maybe I'm over thinking the query... Any insight would be greatly appreciated.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.IPAddress,
tblServiceStartMode.StartMode,
tblFileVersions.FileVersion,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServices.ServiceuniqueID =
tblServicesUni.ServiceuniqueID
Inner Join tblServiceStartMode On tblServiceStartMode.StartID =
tblServices.StartID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblServicesUni.Name = N'CcmExec' And tblFileVersions.FilePathfull Like
'%CcmExec.exe' And (tblFileVersions.Found = 'False' Or tblFileVersions.Found =
'True') And tblAssets.Scanserver Like 'xxxxxxxxxx%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[AnthelioHealth]

That worked perfectly. Thanks a million!

[Daniel_B · Accepted solution]

As you are filtering on both File version and Service name columns, you need to use subqueries:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tService.StartMode,
  tFileversion.FileVersion,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblFileVersions.AssetID,
    tblFileVersions.FileVersion
  From tblFileVersions
  Where tblFileVersions.FilePathfull Like '%CcmExec.exe') tFileversion
    On tblAssets.AssetID = tFileversion.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select tblServices.AssetID,
    tblServiceStartMode.StartMode
  From tblServices
    Inner Join tblServicesUni On tblServices.ServiceuniqueID =
      tblServicesUni.ServiceuniqueID
    Inner Join tblServiceStartMode On tblServiceStartMode.StartID =
      tblServices.StartID
  Where tblServicesUni.Name = N'CcmExec') tService On tService.AssetID =
    tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Scanserver Like 'xxxxxxxxxx%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName