→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
FlagonFly
Engaged Sweeper
Since most of our systems are configured for the "Performance Logs and Alerts" service to be set to Automatic but usually is not in Running state, I'd like to exclude it from the two reports for "Services: Automatic services which are stopped on servers" and "Services: Automatic services which are stopped on clients"

When I add the following line to the clients report just before the Order By, it works great:
And dbo.tblServices.Name <> 'Performance Logs and Alerts'

However, doing the same thing for the servers report still shows all the servers with that service not running.

Here is the full query for the server report where i was also trying a wildcard statement, still not working. Any help greatly appreciated!



Select Top 1000000 dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, dbo.tblOperatingsystem.Description, dbo.tblServices.Caption, dbo.tblServices.Lastchanged, Web40OSName.Compimage As icon From dbo.tblServices Inner Join dbo.tblComputers On dbo.tblServices.Computername = dbo.tblComputers.Computername Inner Join dbo.tblComputersystem On dbo.tblComputers.Computername = dbo.tblComputersystem.Computername Inner Join dbo.tblOperatingsystem On dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername Where dbo.tblServices.Startmode = 'Auto' And dbo.tblServices.State = 'stopped' And dbo.tblServices.Name Not Like 'Performance%' And dbo.tblComputersystem.Domainrole <> 1 Order By dbo.tblComputers.Computer
3 REPLIES 3
Hemoco
Lansweeper Alumni
If you have only english systems you can use the caption, if you have different languages it's better to use dbo.tblServices.Name because this will be the same everywhere.
FlagonFly
Engaged Sweeper
Thanks, I will try that one. Also figured out I was looking at the wrong table. While "Performance Logs and Alerts" is the name of the service when you look in the list, in the database it is saved as the Caption. So I learned that this works:

Select Top 1000000 dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, dbo.tblOperatingsystem.Description, dbo.tblServices.Caption, dbo.tblServices.Lastchanged, Web40OSName.Compimage As icon From dbo.tblServices Inner Join dbo.tblComputers On dbo.tblServices.Computername = dbo.tblComputers.Computername Inner Join dbo.tblComputersystem On dbo.tblComputers.Computername = dbo.tblComputersystem.Computername Inner Join dbo.tblOperatingsystem On dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername Where dbo.tblServices.Caption <> 'Performance Logs and Alerts' And dbo.tblServices.Startmode = 'Auto' And dbo.tblServices.State = 'stopped' And dbo.tblComputersystem.Domainrole <> 1 And dbo.tblServices.Caption <> 'Security Center' Order By dbo.tblComputers.Computer
Hemoco
Lansweeper Alumni
Try this:

Select Top 1000000 dbo.tblComputers.Computername,
dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain,
dbo.tblOperatingsystem.Description, dbo.tblServices.Caption,
dbo.tblServices.Lastchanged, Web40OSName.Compimage As icon
From dbo.tblServices Inner Join
dbo.tblComputers On dbo.tblServices.Computername =
dbo.tblComputers.Computername Inner Join
dbo.tblComputersystem On dbo.tblComputers.Computername =
dbo.tblComputersystem.Computername Inner Join
dbo.tblOperatingsystem On dbo.tblComputers.Computername =
dbo.tblOperatingsystem.Computername Inner Join
dbo.web40ActiveComputers On dbo.tblComputers.Computername =
dbo.web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
Where dbo.tblServices.Startmode = 'Auto' And dbo.tblServices.State = 'stopped'
And dbo.tblServices.Name Not Like 'sysmonlog' And
dbo.tblComputersystem.Domainrole <> 1
Order By dbo.tblComputers.Computer