cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
smozie
Engaged Sweeper
Trying to see if I can condense some of the information in my report. Right now each individual drive has its own row but I want the drives to be listed/grouped together so that I can get an accurate number for how many hosts need to be encrypted as opposed to the total drives that needs to be encrypted.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName
1 REPLY 1
Bruce_B
Lansweeper Alumni
Condensing multiple rows per asset into a single row, when there is non-distinct data is not a straight-forward process and would require some advanced SQL. You may be able to achieve this using STUFF (SQL Server only).