Custom report (top users / computer / department / Desk bank) not showing Administrator

[Globalhelpdesk]

Hi all,

I've recently set up Lansweeper for the company I work for and I'm now trying to get all the requested reporting set up. One such report needs to show computers with their associated 'Top User' (most logons) and the department and exactly what bank of desks they're sat at. I've used the ADcomputers field 'location' to input desk banks and everything is showing how I'd like but the report is not showing any computers where Administrator is the most logged on user and is also missing many computers from other domains / workgroups - only showing results from the domain that the Lansweeper install is on.

Below is the report I'm running:

Select Top 1000000 (Select Top 1 tblCPlogoninfo.Username From tblCPlogoninfo Group By tblCPlogoninfo.Username, tblCPlogoninfo.Computername Having tblCPlogoninfo.Computername = tblComputers.Computername Order By Count(1) Desc) As Owner, tblComputers.ComputerUnique As Computer, tblADusers.Department, tblADComputers.Location From tblComputers Inner Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblADusers On tblComputers.Userdomain = tblADusers.Userdomain And tblADusers.Username = tblComputers.Username Where (Select Top 1 tblCPlogoninfo.Username From tblCPlogoninfo Group By tblCPlogoninfo.Username, tblCPlogoninfo.Computername Having tblCPlogoninfo.Computername = tblComputers.Computername Order By Count(1) Desc) Like '%.%' And tblComputers.ComputerUnique Not Like '%GLA01%'

Now, if I use just 'Username' from ADusers the report will show correctly with Administrators and all. As below:

Select Top 1000000 tblComputers.ComputerUnique As Computer, tblADusers.Username, tblADusers.Department, tblADComputers.Location From tblComputers Inner Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblADusers On tblADusers.Username = tblComputers.Username And tblADusers.Userdomain = tblComputers.Userdomain Where tblComputers.ComputerUnique Not Like '%GLA01%'

Is there something I'm missing here? Bearing in mind that I've not done any SQL / database work before.

Appreciate any and all help!

Thanks,

Wes

[Globalhelpdesk]

Thanks,

That worked a treat!

Wes

[Hemoco]

Please try the report below. It includes computer name, location, top user, top user department and top user logon count.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblADComputers.Location, tblADusers.Username, tblADusers.Userdomain, tblADusers.Department, Max(LogonsCount.[Total Logons]) As [Top User Logons] From tblComputers Inner Join (Select Top 1000000 tblCPlogoninfo.Computername, tblCPlogoninfo.Username, tblCPlogoninfo.Domain, Count(tblCPlogoninfo.logontime) As [Total Logons] From tblComputers Inner Join tblCPlogoninfo On tblComputers.Computername = tblCPlogoninfo.Computername Group By tblCPlogoninfo.Computername, tblCPlogoninfo.Username, tblCPlogoninfo.Domain Order By tblCPlogoninfo.Computername, Count(tblCPlogoninfo.logontime) Desc) LogonsCount On LogonsCount.Computername = tblComputers.Computername Left Join tblADusers On tblADusers.Username = tblComputers.Username And tblADusers.Userdomain = tblComputers.Userdomain Left Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Group By tblComputers.Computername, tblComputers.ComputerUnique, tblADComputers.Location, tblADusers.Username, tblADusers.Userdomain, tblADusers.Department Order By tblComputers.ComputerUnique