ConfigMgr: SQL to find a service that uses a specific EXE

I was asked to find servers that had the “Apache HTTP Server” service installed and was told that I could identify the service by either httpd.exe or apache.exe.  Additionally, the requester only wanted servers for a specific organizational unit “ABC”.

select
ou.System_OU_Name0
, s.Name0
, s.operatingSystem0
, sf.FilePath + sf.[FileName] [File]
, sf.FileDescription
, sf.FileVersion
, srvc.Name0 ServiceName0
, srvc.Status0
, srvc.State0
, srvc.StartName0
, srvc.StartMode0
, srvc.PathName0
from v_GS_SoftwareFile sf
inner join v_RA_System_SystemOUName ou on ou.ResourceID = sf.ResourceID
inner join v_R_System s on s.ResourceID = sf.ResourceID
inner join v_GS_SYSTEM syst on syst.ResourceID = sf.ResourceID
inner join v_GS_SERVICE srvc on srvc.ResourceID = sf.ResourceID
and (
srvc.PathName0 like '"' + sf.FilePath + sf.[FileName] +'%'
or srvc.PathName0 like sf.FilePath + sf.[FileName] +'%'
)
where
sf.[FileName] in ('apache.exe', 'httpd.exe')
and ou.System_OU_Name0 = 'DOMAIN.NAME/DEPARTMENTS/ABC'
and syst.SystemRole0 = 'Server'
order by s.Name0

Leave a comment