Report - Request

[nyra_mtobias]

I am looking for the following report, but to include OS.

If possible to include Processor.

Thanks!

[RCorbeil]

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]

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]

Which report are you looking for?