Windows computers that do not have a specific service installed

[Susan_A]

The report below lists Windows computers that are missing a specific service. Replace YourService with the internal name of the service. This is the Service Name value you see when running services.msc, right-clicking the service and selecting Properties. The internal name of the Windows Update service is wuauserv for instance.

The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The computer does not have the specified service installed.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblServices.AssetID
  From tblServices Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
      tblServices.ServiceuniqueID
  Where tblServicesUni.Name Like '%YourService%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[jbeach]

Susan.A wrote:
The report below lists Windows computers that are missing a specific service. Replace YourService with the internal name of the service. This is the Service Name value you see when running services.msc, right-clicking the service and selecting Properties. The internal name of the Windows Update service is wuauserv for instance.

The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The computer does not have the specified service installed.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblServices.AssetID
  From tblServices Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
      tblServices.ServiceuniqueID
  Where tblServicesUni.Name Like '%YourService%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

Hello,

How would I alter this query to only include workstations and not servers on my domain. Thank you!

[zberkshier91]

How would I structure this for finding multiple services that are not installed?

[Susan_A]

Yes, something like this will work as well:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets a
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where not exists (Select 1
  From tblServices b Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
      tblServices.ServiceuniqueID
  Where tblServicesUni.Name Like '%YourService%' and a.assetid = b.assetid) And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[Okan]

Is it possible to write this query using NOT EXISTS instead of NOT IN?