cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tomas
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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')

View solution in original post

2 REPLIES 2
MikeMc
Champion Sweeper II
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
Engaged Sweeper III
No one? 😕