→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dsulliva053117
Engaged Sweeper
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?
2 REPLIES 2
dsulliva053117
Engaged Sweeper
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
Honored Sweeper II
Take a look at this thread for an approach.