An example of such a report can be found below. The report works with the ROW_NUMBER function to be able to limit the results displayed per asset. More information about the function can be found here: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
subquery1.Username,
subquery1.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
subquery1.logontime,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select Row_Number() Over (Partition By tblCPlogoninfo.AssetID Order
By tblCPlogoninfo.AssetID) As RowNumber,
tblCPlogoninfo.AssetID,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username
From tblCPlogoninfo) As subquery1 On subquery1.AssetID = tblAssets.AssetID
Where subquery1.RowNumber < 4 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
subquery1.logontime Desc