All SQL server editions with state

[gli]

I need a report with all SQL server and editions with service state.

if i take the table tblService,tblServiceUni with Like SQL Server (% and tblServiceState i see in the report for all sql more then one state:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblSqlServers.displayVersion As Edition,
tblSqlServers.skuName As License,
tblSqlServers.spLevel As [SQL SP],
tblSqlServers.serviceName As Service,
tblSqlServers.lastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblServicesUni.Caption,
tblServiceState.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Where tblServicesUni.Caption Like 'SQL Server (%'
Order By tblAssets.AssetName,
Edition

[Nick_VDB]

If the servers have multiple SQL Server services then it is expected behaviour to have multiple rows for a single asset. Also, for each service that is running you will get back the 'Captions' of the service. Each service with a different caption can also have a different state.