cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AnthelioHealth
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
AnthelioHealth
Engaged Sweeper II
That worked perfectly. Thanks a million!
Daniel_B
Lansweeper Alumni
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