
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-12-2013 04:12 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-12-2013 04:54 PM
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'
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'
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-12-2013 04:54 PM
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'
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'
