→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bpickens
Engaged Sweeper
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.
1 REPLY 1
Andy_Sismey
Champion Sweeper III
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