To use the SMS SQL database to find those machines that are running a specified service you must remember that the SMS inventory you are requesting is only as good as the last time the inventory was run on the client machine. This means simply that the service was running when the inventory was obtained however the service may have been terminated moments after the inventory was collected.
You must also specify in your query that you only want those machines with the specified service listed AND have the service started state of 1 (On).
The example below uses the FTP Publishing service and can be changed as needed. If you want to use the service name rather than the service display name change the line that reads:
Where Services.DisplayName0 = 'FTP Publishing Service' to
Where Services. Name0 = ' MSFtpsvc'
SQL Query:
Select
SD.Name0 'Machine Name',
Services.Name0 'Service Name',
Services.DisplayName0 'Display Name',
Services.Started0
From System_Disc SD
Join Services_DATA Services on SD.ItemKey = Services.MachineID
Where Services.DisplayName0 = 'FTP Publishing Service'
And Services.Started0 = 1
No Comments