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 Close ticket failed
by  JohnnyL   Go to last post Go to first unread
Last post: Yesterday at 6:51:21 PM(UTC)
Lansweeper SNMP Asset dont work
by  implenia  
Go to last post Go to first unread
Last post: Yesterday at 3:44:02 PM(UTC)
Lansweeper Remote IT Asset MGMT during the Covid-19 Crisis
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 1:14:27 PM(UTC)
Lansweeper Unable to transfer user's data prior to removal
by  jbruyet  
Go to last post Go to first unread
Last post: 4/1/2020 11:40:35 PM(UTC)
Lansweeper Scanning IP Range Subnet
by  ethanthekiwi   Go to last post Go to first unread
Last post: 4/1/2020 9:16:35 PM(UTC)
Lansweeper Toner Levels Missing
by  brodiemac-too  
Go to last post Go to first unread
Last post: 4/1/2020 8:42:19 PM(UTC)
Lansweeper WLC 5508 question
by  Faktotum   Go to last post Go to first unread
Last post: 4/1/2020 1:10:23 PM(UTC)
Lansweeper SNMPv3 global credentials ?
by  JeremySG  
Go to last post Go to first unread
Last post: 4/1/2020 9:01:16 AM(UTC)