→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Susan_A
Lansweeper Alumni
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
4 REPLIES 4
jbeach
Engaged Sweeper
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
Engaged Sweeper II
How would I structure this for finding multiple services that are not installed?
Susan_A
Lansweeper Alumni
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
Engaged Sweeper III
Is it possible to write this query using NOT EXISTS instead of NOT IN?