SCCM Service not installed

[plachno]

I modified a script to identify when the SCCM service is not running. (below) I have ben trying for days to create a report to identify machines without the SCCM service installed.

Any assitance with creating a report to idenitfy machines without the SCCM service would me MUCH APPRECIATED!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblServicesUni.Name As Service,
tblServices.Lastchanged,
tsysOS.Image As icon,
tblAssets.Username
From tblAssets
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServices.ServiceuniqueID =
tblServicesUni.ServiceuniqueID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblServicesUni.Name = N'CcmExec' And tblServices.Started = 0 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Hemoco · Accepted solution]

Try:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select tblServices.AssetID
From tblServices Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Name = 'CcmExec') And tblAssets.Lastseen Is Not Null And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName