Help with reportbuilder

[NeilL]

Hi,

I'm trying to generate a report that shows the following information,

Computer Name, Computer Model, Computer Serial, Last Known IP, Last user, Monitor Model, Monitor Serial but for some reason it creating duplicate records when I use report builder.

Select Top 1000000 dbo.tblComputers.Computer As [Computer Name],
dbo.tblComputersystem.Model As [Computer Model], tblBIOS.SerialNumber As
[Computer Serial], dbo.tblComputers.LastknownIP As [Last Known IP],
tblMonitor.MonitorModel As [Monitor Model], tblMonitor.SerialNumber As
[Monitor Serial], tblCPlogoninfo.Username
From dbo.tblComputersystem Inner Join
dbo.tblComputers On dbo.tblComputersystem.Computername =
dbo.tblComputers.Computername Inner Join
tblBIOS On dbo.tblComputers.Computername = tblBIOS.Computername Inner Join
tblNetwork On dbo.tblComputers.Computername = tblNetwork.Computername
Inner Join
tblMonitor On dbo.tblComputers.Computername = tblMonitor.Computername
Full Join
tblCPlogoninfo On dbo.tblComputers.Computername = tblCPlogoninfo.Computername
Inner Join
tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername
Inner Join
tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername
Group By dbo.tblComputers.Computer, dbo.tblComputersystem.Model,
tblBIOS.SerialNumber, dbo.tblComputers.LastknownIP, tblMonitor.MonitorModel,
tblMonitor.SerialNumber, tblCPlogoninfo.Username
Having dbo.tblComputers.Computer Like 'YZZ%'
Order By dbo.tblComputers.Computer

Any help would be greatly appreciated.

Thanks
Neil

[Hemoco]

tblcomputers

field: username, userdomain

[Hemoco]

try this

Select Top 1000000 dbo.tblComputers.Computer As [Computer Name],
dbo.tblComputersystem.Model As [Computer Model], tblBIOS.SerialNumber As
[Computer Serial], dbo.tblComputers.LastknownIP As [Last Known IP],
tblMonitor.MonitorModel As [Monitor Model], tblMonitor.SerialNumber As
[Monitor Serial], tblComputers.username
From dbo.tblComputersystem Inner Join
dbo.tblComputers On dbo.tblComputersystem.Computername =
dbo.tblComputers.Computername Inner Join
tblBIOS On dbo.tblComputers.Computername = tblBIOS.Computername Inner Join
tblMonitor On dbo.tblComputers.Computername = tblMonitor.Computername
Inner Join
tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername
Group By dbo.tblComputers.Computer, dbo.tblComputersystem.Model,
tblBIOS.SerialNumber, dbo.tblComputers.LastknownIP, tblMonitor.MonitorModel,
tblMonitor.SerialNumber, tblComputers.username
Having dbo.tblComputers.Computer Like 'YZZ%'
Order By dbo.tblComputers.Computer

[NeilL]

Lansweeper wrote:
try this

Select Top 1000000 dbo.tblComputers.Computer As [Computer Name],
dbo.tblComputersystem.Model As [Computer Model], tblBIOS.SerialNumber As
[Computer Serial], dbo.tblComputers.LastknownIP As [Last Known IP],
tblMonitor.MonitorModel As [Monitor Model], tblMonitor.SerialNumber As
[Monitor Serial], tblComputers.username
From dbo.tblComputersystem Inner Join
dbo.tblComputers On dbo.tblComputersystem.Computername =
dbo.tblComputers.Computername Inner Join
tblBIOS On dbo.tblComputers.Computername = tblBIOS.Computername Inner Join
tblMonitor On dbo.tblComputers.Computername = tblMonitor.Computername
Inner Join
tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername
Group By dbo.tblComputers.Computer, dbo.tblComputersystem.Model,
tblBIOS.SerialNumber, dbo.tblComputers.LastknownIP, tblMonitor.MonitorModel,
tblMonitor.SerialNumber, tblComputers.username
Having dbo.tblComputers.Computer Like 'YZZ%'
Order By dbo.tblComputers.Computer

It looks like it worked, where in the tables does it store the last user that logged into the computer?

Thanks
Neil

[Hemoco]

Remove table "tblnetwork"

[NeilL]

Lansweeper wrote:
Remove table "tblnetwork"

I removed this but I still getting 10 records for one computer where all I need is the last user who logged in.

This is an example of what I'm getting

Name Model Serial Last IP Monitor Serial Username
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 Admin1
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 user1
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 user2
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 user3
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 user4
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 Admin1
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 user1
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 user2
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 user3
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 user4

What I looking for is that user 3 was the last to login and it would be like this
Name Model Serial Last IP Monitor Serial Username
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q045301389 user3
YZZ0022000 9074Y7S ADJIL6C xxx.xxx.xxx.xxx VE710b-2 P1Q050313494 user3

since this computer has dual monitors

Thanks
Neil