cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
nyra_mtobias
Engaged Sweeper
I am looking for the following report, but to include OS.

If possible to include Processor.

Thanks!
3 REPLIES 3
RCorbeil
Honored Sweeper II
Add the extra elements to SubQuery3, where the other asset properties are being retrieved.
Select Top 1000000
tblADusers.Username,
tblADusers.Userdomain,
SubQuery2.Count As UniqueComputers,
SubQuery3.AssetID,
SubQuery3.AssetName,
SubQuery3.OSname,
SubQuery3.Version,
SubQuery3.Processor,
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,
tsysOS.OSname,
tblAssets.Version,
tblAssets.Processor,
tblAssets.Domain,
tblAssets.IPAddress,
Max(tblCPlogoninfo.logontime) As LastLogon
From
tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
Group By
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Version,
tblAssets.Processor,
tblAssets.Domain,
tblAssets.IPAddress) SubQuery3 On SubQuery3.Username = tblADusers.Username
And SubQuery3.Userdomain = tblADusers.Userdomain
Order By
tblADusers.Userdomain,
tblADusers.Username,
SubQuery3.LastLogon Desc
nyra_mtobias
Engaged Sweeper
Similar to the following.

https://www.lansweeper.com/Forum/yaf_postst12202_Number-of-computers-users-are-logging-into.aspx#post44201

Including OS and Processor.
brandon_jones
Champion Sweeper III
Which report are you looking for?