cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
iatech
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
iatech
Engaged Sweeper II
Thank you. Exactly what I needed.
Susan_A
Lansweeper Alumni
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