Notification

Icon
Error

Windows computers that do not have a specific service installed

Posted: Thursday, December 4, 2014 11:40:14 AM(UTC)
Susan.A

Susan.A

Member Administration Original PosterPosts: 1,536
1
Like
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.

Code:
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 '%[h]YourService[/h]%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
Okan
#1Okan Member Posts: 26  
posted: 4/10/2016 1:33:57 PM(UTC)
Is it possible to write this query using NOT EXISTS instead of NOT IN?
Susan.A
#2Susan.A Member Administration Original PosterPosts: 1,536  
posted: 4/17/2016 10:11:53 PM(UTC)
Yes, something like this will work as well:
Code:
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 '%[h]YourService[/h]%' and a.assetid = b.assetid) And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
zberkshier91
#3zberkshier91 Member Posts: 13  
posted: 9/20/2016 3:07:08 PM(UTC)
How would I structure this for finding multiple services that are not installed?
Jon Beach
#4Jon Beach Member Posts: 1  
posted: 2/18/2020 9:01:45 PM(UTC)
Originally Posted by: Susan.A Go to Quoted Post
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.

Code:
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 '%[h]YourService[/h]%') 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!

Active Discussions

Lansweeper NOT doesn't work as expected
by  KevinA-REJIS   Go to last post Go to first unread
Last post: Yesterday at 5:49:29 PM(UTC)
Lansweeper Combined Vulnerability Report Q1 2019
by  Leslie Stroobant  
Go to last post Go to first unread
Last post: Yesterday at 3:27:42 PM(UTC)
Lansweeper All Devices with WINS Service Running on the Network
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 2:44:56 PM(UTC)
Lansweeper Physical Laptops and Desktops Only
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 1:51:56 PM(UTC)
Lansweeper Assistance With AV & Bitlocker Report
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 12:50:47 PM(UTC)
Lansweeper Count of completed KB installation Report
by  jamie21  
Go to last post Go to first unread
Last post: 6/4/2020 11:49:57 AM(UTC)
Lansweeper local admin users of a specific device
by  Andy.S   Go to last post Go to first unread
Last post: 6/4/2020 10:42:23 AM(UTC)
Lansweeper Bitlocker Report not contain all computers
by  Andy.S  
Go to last post Go to first unread
Last post: 6/3/2020 6:20:53 PM(UTC)