Reporting if the service is or is not present and its current state

[bpickens]

All,

Kind of been racking my brains.
I am trying to run a report to look for a specific windows service "Carbon Matter" and report if the service exist and what is its current state. If the service does not exist it to report "Not found" in the service name field.

Sample Output.

Assetname, Service Name, Start Mode, State
LAP001, Carbon Matter, Auto, Running
LAP002, Carbon Matter, Auto, Stopped
LAP003, Not Found, Not Found, Not Found

Any help is greatly appreciated.

[Andy_Sismey]

Give this ago , just change the Service Name as required :

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When Service.Name Is Null Then 'Not Found'
    Else Service.Name
  End As Service,
  Service.StartMode,
  Service.State,
  Service.Caption,
  Service.Startname
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblServiceStartMode.StartMode,
      tblServices.AssetID,
      tblServicesUni.Caption,
      tblServicesUni.Name,
      tblServiceState.State,
      tblServicesUni.Startname
    From lansweeperdb.dbo.tblServiceStartMode
      Inner Join lansweeperdb.dbo.tblServices On tblServiceStartMode.StartID =
          tblServices.StartID
      Inner Join lansweeperdb.dbo.tblServicesUni On
          tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
      Inner Join lansweeperdb.dbo.tblServiceState On tblServiceState.StateID =
          tblServices.StateID
    Where tblServicesUni.Caption = 'Microsoft Defender Antivirus Service')
  Service On Service.AssetID = tblassets.AssetID
Where tsysassettypes.AssetTypename Like '%Win%' And tblassetcustom.State = 1