Report for service status

[dsulliva053117]

Hello, I'm trying to build a report that can have multiple services as seperate columns, with the status of that service shown in the report.. For example, something that would look like this

AssetID Service1 Service2 Service3
computer1 Running Running Stopped
computer2 Running Running Running

I can get it to read the status of the services fine, but can't figure out how to get a seperate column for each service. Any ideas?

[dsulliva053117]

Thank You. So, I was able to get the columns correct, however, its adding a separate line for each service its checking:

(See screenshot attached)

The goal is to get this all into one row... Anyone know what I'm doing wrong? Something with the joins? sql report code below:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
Max(Case
When tblServicesUni.Name = 'Service1' And tblServiceState.State =
'Running' Then 'Running'
When tblServicesUni.Name = 'Service1' And tblServiceState.State !=
'Running' Then 'Stopped'
Else Null
End) As Kavoom,
Max(Case
When tblServicesUni.Name = 'Service2' And tblServiceState.State =
'Running' Then 'Running'
When tblServicesUni.Name = 'Service2' And tblServiceState.State !=
'Running' Then 'Stopped'
Else Null
End) As Evidian,
Max(Case
When tblServicesUni.Name = 'Service3' And tblServiceState.State =
'Running' Then 'Running'
When tblServicesUni.Name = 'Service3' And tblServiceState.State !=
'Running' Then 'Stopped'
Else Null
End) As Symantec,
Case
When Right(tsysOS.OScode, 5) = '15063' Then 'Build 1703'
When Right(tsysOS.OScode, 5) = '16299' Then 'Build 1709'
When Right(tsysOS.OScode, 5) = '14393' Then 'Build 1607'
When Right(tsysOS.OScode, 5) = '17134' Then 'Build 1803'
Else Right(tsysOS.OScode, 5)
End As Win10_Build,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Model,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As Uptime,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.SP As SP,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
tblDiskdrives.Lastchanged As LastChanged
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServices.ServiceuniqueID =
tblServicesUni.ServiceuniqueID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblAssets.AssetName Not Like '%W' And tblAssets.AssetName Not Like 'BR%'
Group By tsysOS.Image,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.SP,
tblDiskdrives.Caption,
tblDiskdrives.Lastchanged,
tblServicesUni.Name,
tblServiceState.State,
tsysOS.OScode,
tblAssets.Uptime,
tblDiskdrives.Freespace,
tblDiskdrives.Size,
tsysIPLocations.LocationID

[RCorbeil]

Take a look at this thread for an approach.