Most logged in User for asset

[Tomas]

As it says in the description I Need to add a Colum with the User that has logged in most to an Asset.
I think everything else is ok ^^
Help would be very much appreciated.

Select Distinct Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypename,
  tblAssets.Lastseen,
  tblAssets.AssetName As Hostname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  TsysChassisTypes.ChassisName As ComputerType,
  tblAssetCustom.Model As ModelName,
  tblAssetCustom.Serialnumber,
  tblAssets.Username As UserAccount,
  tblAssetCustom.Manufacturer,
  tblOperatingsystem.Caption As [Operating system],
  Case tblAssetCustom.Model When 'VMware Virtual Platform' Then 'Yes'
    When 'Virtual Machine' Then 'Yes' Else 'No' End As [Virtual machine],
  tblAssets.IPAddress,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
    TsysChassisTypes.Chassistype
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where (tsysAssetTypes.AssetTypename = 'Windows') Or
  (tsysAssetTypes.AssetTypename = 'Linux')

This report does already what I want but per User. I would need the most logged in user per Asset.

If someone could help me rewrite this Query to get the response I want. I'm sure I can merge it with my main report.

Select Top 1000000 SubQuery2.Username,
  SubQuery2.Userdomain,
  tblADusers.Department,
  SubQuery2.LogonCount,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.AssetID,
  tblAssets.IPAddress
From (Select Top 1000000 SubQuery.Username,
    SubQuery.Userdomain,
    Max(SubQuery.Count) As LogonCount
  From (Select Top 1000000 tblCPlogoninfo.Username,
      tblCPlogoninfo.Domain As Userdomain,
      tblCPlogoninfo.AssetID As ID,
      Count(tblCPlogoninfo.ID) As Count
    From tblCPlogoninfo
    Group By tblCPlogoninfo.Username,
      tblCPlogoninfo.Domain,
      tblCPlogoninfo.AssetID) SubQuery
  Group By SubQuery.Username,
    SubQuery.Userdomain) SubQuery2
  Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain As Userdomain,
    tblCPlogoninfo.AssetID As ID,
    Count(tblCPlogoninfo.ID) As Count
  From tblCPlogoninfo
  Group By tblCPlogoninfo.Username,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.AssetID) SubQuery3 On SubQuery3.Username = SubQuery2.Username
    And SubQuery3.Userdomain = SubQuery2.Userdomain And SubQuery3.Count =
    SubQuery2.LogonCount
  Inner Join tblAssets On tblAssets.AssetID = SubQuery3.ID
  Inner Join tblADusers On tblADusers.Username = SubQuery2.Username And
    tblADusers.Userdomain = SubQuery2.Userdomain
Order By SubQuery2.Userdomain,
  SubQuery2.Username

[MikeMc · Accepted solution]

I'm no SQL expert, but you are welcome to try this modified query with the subquery I use to get the most frequent users per asset.

SELECT DISTINCT TOP 1000000 tblAssets.AssetID
	,tsysAssetTypes.AssetTypename
	,tblAssets.Lastseen
	,tblAssets.AssetName AS Hostname
	,tsysAssetTypes.AssetTypeIcon10 AS icon
	,TsysChassisTypes.ChassisName AS ComputerType
	,tblAssetCustom.Model AS ModelName
	,tblAssetCustom.Serialnumber
	,tblAssets.Username AS UserAccount
	,REVERSE(STUFF(REVERSE((
					SELECT x.Username + ', '
					FROM (
						SELECT cpli.Username
							,count(*) AS name_count
							,rank() OVER (
								ORDER BY count(*) DESC
								) AS rank
						FROM tblCPlogoninfo cpli
						WHERE cpli.AssetID = tblAssets.AssetID
						GROUP BY cpli.username
						) x
					WHERE x.rank = 1
					FOR XML PATH('')
					)), 1, 2, '')) AS MostFrequentUsers
	,tblAssetCustom.Manufacturer
	,tblOperatingsystem.Caption AS [Operating system]
	,CASE tblAssetCustom.Model
		WHEN 'VMware Virtual Platform'
			THEN 'Yes'
		WHEN 'Virtual Machine'
			THEN 'Yes'
		ELSE 'No'
		END AS [Virtual machine]
	,tblAssets.IPAddress
	,tblADComputers.OU
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
LEFT JOIN tblOperatingsystem ON tblAssets.AssetID = tblOperatingsystem.AssetID
LEFT JOIN tblSystemEnclosure ON tblAssets.AssetID = tblSystemEnclosure.AssetID
LEFT JOIN TsysChassisTypes ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
LEFT JOIN tblNetwork ON tblAssets.AssetID = tblNetwork.AssetID
LEFT JOIN tblADComputers ON tblAssets.AssetID = tblADComputers.AssetID
WHERE (tsysAssetTypes.AssetTypename = 'Windows')
	OR (tsysAssetTypes.AssetTypename = 'Linux')

[Tomas]

No one? 😕