→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Lindner
Engaged Sweeper III
Hi,

i need a report to show, if a service is installed and what state it has.
I think i need a subquery to select that one service and its state.

But i don't know how to create a subquery...
Can someone show me, may be an example...

kindly regards
Andreas
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
A sample report can be seen below. Replace YourService with the name of the service you would like to report on. You can verify what the exact service name is in the Config\Windows\Services section of your assets' Lansweeper webpages.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery.Caption,
SubQuery.Startname,
SubQuery.StartMode,
SubQuery.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblServices.AssetID,
tblServicesUni.Caption,
tblServicesUni.Startname,
tblServiceStartMode.StartMode,
tblServiceState.State
From tblServices
Inner Join tblServiceStartMode On tblServiceStartMode.StartID =
tblServices.StartID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Caption = 'YourService') SubQuery
On SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
A sample report can be seen below. Replace YourService with the name of the service you would like to report on. You can verify what the exact service name is in the Config\Windows\Services section of your assets' Lansweeper webpages.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery.Caption,
SubQuery.Startname,
SubQuery.StartMode,
SubQuery.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblServices.AssetID,
tblServicesUni.Caption,
tblServicesUni.Startname,
tblServiceStartMode.StartMode,
tblServiceState.State
From tblServices
Inner Join tblServiceStartMode On tblServiceStartMode.StartID =
tblServices.StartID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Caption = 'YourService') SubQuery
On SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName