→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎06-20-2016 02:14 PM
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')
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
Solved! Go to Solution.
‎07-01-2016 07:35 PM
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')
‎07-01-2016 07:35 PM
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')
‎06-24-2016 10:31 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now