need Report with subquery

[Lindner]

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

[Hemoco · Accepted solution]

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