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 Ticket Content Default Value
by  CPG   Go to last post Go to first unread
Last post: Yesterday at 8:45:12 PM(UTC)
Lansweeper New status to mimic Closed
by  chris.anderson  
Go to last post Go to first unread
Last post: Yesterday at 7:14:27 PM(UTC)
Lansweeper Active Directory Groups not scanned properly
by  cross_eur   Go to last post Go to first unread
Last post: Yesterday at 5:45:00 PM(UTC)
Lansweeper Change Management - Voting and Tracking
by  brodiemac-too  
Go to last post Go to first unread
Last post: Yesterday at 2:48:01 PM(UTC)
Lansweeper No incoming Mails after update 8.4.100.9
by  EDELL   Go to last post Go to first unread
Last post: Yesterday at 2:38:50 PM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: Yesterday at 9:30:32 AM(UTC)
Lansweeper how to scan intune managed win10 clients?
by  brodiemac-too   Go to last post Go to first unread
Last post: 7/27/2021 9:17:07 PM(UTC)
Lansweeper Sort by in Helpdesk
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/27/2021 9:15:05 PM(UTC)