Number of computers users are logging into

[iatech]

I am trying to find out who is logging into which machines, with a count of how many different machines that they log into, with a date and time they logged in.

[iatech]

Thank you. Exactly what I needed.

[Susan_A · Accepted solution]

I split your post into a separate forum topic and included a sample report below. It lists your domain users, a count of the number of unique computers each user logged into and the last logon event per computer and per user.

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