Active Computers and Top User

[Arv2816]

I'm looking for a way to generate a report that will display all active computers and the top user associated with that computer.

At present when I add a user field to the active computer report I get multiple users associated with a machine, how can I get an output that only shows the top user that has logged into a computer. If that is not possible, is there a way to only display the last logged on user?

Any help is greatly appreciated.

[Arv2816]

Sorry for the slow response. This worked great! Thank you very much.

[Hemoco · Accepted solution]

Can you try this one:

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblComputers.Userdomain, tblComputers.Username,
tblADusers.Displayname, Topuser.Domain As TopDomain, Topuser.Username As
TopUsername, tblADusers1.Displayname As TopDisplayname, Topuser.Total
From tblComputers Left Join
tblADusers On tblADusers.Username = tblComputers.Username And
tblADusers.Userdomain = tblComputers.Userdomain Left Join
(Select Top 1 tblCPlogoninfo.Domain, tblCPlogoninfo.Username,
Count(tblCPlogoninfo.ID) As Total
From tblCPlogoninfo
Group By tblCPlogoninfo.Domain, tblCPlogoninfo.Username
Order By Count(tblCPlogoninfo.ID) Desc) Topuser On Topuser.Domain =
tblComputers.Userdomain And Topuser.Username = tblComputers.Username
Left Join
tblADusers tblADusers1 On tblADusers1.Username = Topuser.Username And
tblADusers1.Userdomain = Topuser.Domain

[Arv2816]

When I say top users, I mean the user account that has the most logons. In SCCM it is referred to as the top console user.

[Hemoco]

What exactly is your definition of the "top user"?