‎12-23-2015 05:43 PM
Solved! Go to Solution.
‎12-24-2015 01:30 PM
Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
SubQuery2.Count As UniqueComputers,
SubQuery3.AssetID,
SubQuery3.AssetName,
SubQuery3.Domain,
SubQuery3.IPAddress,
SubQuery3.LastLogon
From tblADusers
Left Join (Select Top 1000000 SubQuery1.Username,
SubQuery1.Userdomain,
Count(SubQuery1.AssetID) As Count
From (Select Distinct Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblAssets.AssetID
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID)
SubQuery1
Group By SubQuery1.Username,
SubQuery1.Userdomain) SubQuery2 On SubQuery2.Username = tblADusers.Username
And SubQuery2.Userdomain = tblADusers.Userdomain
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress) SubQuery3 On SubQuery3.Username = tblADusers.Username
And SubQuery3.Userdomain = tblADusers.Userdomain
Order By tblADusers.Userdomain,
tblADusers.Username,
SubQuery3.LastLogon Desc
‎12-24-2015 01:47 PM
‎12-24-2015 01:30 PM
Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
SubQuery2.Count As UniqueComputers,
SubQuery3.AssetID,
SubQuery3.AssetName,
SubQuery3.Domain,
SubQuery3.IPAddress,
SubQuery3.LastLogon
From tblADusers
Left Join (Select Top 1000000 SubQuery1.Username,
SubQuery1.Userdomain,
Count(SubQuery1.AssetID) As Count
From (Select Distinct Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblAssets.AssetID
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID)
SubQuery1
Group By SubQuery1.Username,
SubQuery1.Userdomain) SubQuery2 On SubQuery2.Username = tblADusers.Username
And SubQuery2.Userdomain = tblADusers.Userdomain
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress) SubQuery3 On SubQuery3.Username = tblADusers.Username
And SubQuery3.Userdomain = tblADusers.Userdomain
Order By tblADusers.Userdomain,
tblADusers.Username,
SubQuery3.LastLogon Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now