Find computers without these Services

[SCRAWNZ]

The code below lists all computers with the services 'McAfee Endpoint Encryption Agent' or 'SafeGuard Easy Control' installed. I want a report which list systems with neither of these services installed.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblServices.Lastchanged,
tsysOS.Image As icon,
tblServicesUni.Name,
tblServicesUni.Caption
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'McAfee Endpoint Encryption Agent' Or
tblServicesUni.Caption = N'SafeGuard Easy Control') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[SCRAWNZ · Accepted solution]

This seemed to work

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where (tblAssets.AssetID Not In (Select Top 1000000 tblServices.AssetID
From tblServices Inner Join tblServiceState On tblServiceState.StateID =
tblServices.StateID Inner Join tblServicesUni
On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
Where tblServicesUni.Name = 'McAfee Endpoint Encryption Agent' And
tblServiceState.State = 'running') And tblAssets.AssetID Not In (Select Top
1000000 tblServices.AssetID
From tblServices Inner Join tblServiceState On tblServiceState.StateID =
tblServices.StateID Inner Join tblServicesUni
On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
Where tblServicesUni.Caption = 'SafeGuard Easy Control' And
tblServiceState.State = 'running') And tsysAssetTypes.AssetTypename =
'windows') And tsysAssetTypes.AssetTypename = 'windows'