Lansweeper logo
Home Download Features Demo Buy now Help Support forum
 
    Most requested support articles:
  Lansweeper troubleshooting guide.
  The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
  WMI Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
  How to configure the windows firewall using group policies.
  Support:  
 support@lansweeper.com  
Skype: Lansweeper  
  Mo-Fri 9h-17h CET  
Welcome Guest Search | Active Topics | Log In | Register

Untag as favorite
Problem with repeating results
Options
Previous Topic Next Topic
lukee20
#1 Posted : Monday, November 14, 2011 3:51:40 AM

Rank: Premium user

Groups: Member, Premium Users
Posts: 13
Location: Melbourne
Hi,

I am trying to write a report detailing all products keys for some Microsoft products. But for some reason the reports contains the exact same user/pc multiple times, creating one massive report! Can anyone help?

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Username, tblADusers.Displayname, tblSerialnumber.ProductKey,
tblSerialnumber.Product
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain Inner Join
tblSerialnumber On tblComputers.Computername = tblSerialnumber.Computername
Where (tblSerialnumber.Product Like 'Microsoft Office Project%') Or
(tblSerialnumber.Product Like 'Microsoft Office Visio%') Or
(tblSerialnumber.Product Like 'Microsoft Project%')
Back to top
Lansweeper
#2 Posted : Monday, November 14, 2011 3:51:20 PM

Rank: Administration

Groups: Administration, Premium Users
Posts: 10,378
Could you try the following report instead:

Quote:
Select Distinct Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Username, tblADusers.Displayname, tblSerialnumber.ProductKey,
tblSerialnumber.Product
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain Inner Join
tblSerialnumber On tblComputers.Computername = tblSerialnumber.Computername
Where (tblSerialnumber.Product Like 'Microsoft Office Project%') Or
(tblSerialnumber.Product Like 'Microsoft Office Visio%') Or
(tblSerialnumber.Product Like 'Microsoft Project%')
Back to top
AngelEye88NL
#3 Posted : Friday, November 25, 2011 3:29:48 PM

Rank: Premium user

Groups: Member, Premium Users
Posts: 5
Location: the Netherlands
I have the same problem. I made a report with the computer name, date of last seen, IP address, total amount of memory and the CPU, but I get a giant report with alot of double results.

Distinct is already added. The report:

Quote:
Select Distinct Top 1000000 tblcomputers.ComputerUnique, tblcomputers.Computername, tblcomputers.LastknownIP, tblcomputers.Lastseen, web40CorrectMemory.Memory, web40ProcessorCapacity.CPU From tblcomputers, web40CorrectMemory, web40ProcessorCapacity Where tblcomputers.LastknownIP Like '192.168.30.%'
Back to top
Lansweeper
#4 Posted : Friday, November 25, 2011 3:37:11 PM

Rank: Administration

Groups: Administration, Premium Users
Posts: 10,378
AngelEye88NL wrote:
I have the same problem. I made a report with the computer name, date of last seen, IP address, total amount of memory and the CPU, but I get a giant report with alot of double results.

Distinct is already added. The report:

Quote:
Select Distinct Top 1000000 tblcomputers.ComputerUnique, tblcomputers.Computername, tblcomputers.LastknownIP, tblcomputers.Lastseen, web40CorrectMemory.Memory, web40ProcessorCapacity.CPU From tblcomputers, web40CorrectMemory, web40ProcessorCapacity Where tblcomputers.LastknownIP Like '192.168.30.%'


Please try the report below. You did not link your tables to tblComputers, which is necessary.

When linking tables to tblComputers you will be most commonly linking the “Computername” fields in both tables. You do this by “grabbing” (left-click and hold) the Computername field from one table and dragging it to the Computername name field of the other table. Let go of your left mouse button to see the connection appear. Right-click on a connection and choose Remove to delete it.

Quote:
Select Distinct Top 1000000 tblComputers.ComputerUnique,
tblComputers.Computername, tblComputers.LastknownIP, tblComputers.Lastseen,
web40CorrectMemory.Memory, web40ProcessorCapacity.CPU
From tblComputers Inner Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Inner Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Where tblComputers.LastknownIP Like '192.168.30.%'
Back to top
| Edit by user
AngelEye88NL
#5 Posted : Friday, November 25, 2011 4:33:21 PM

Rank: Premium user

Groups: Member, Premium Users
Posts: 5
Location: the Netherlands
Lansweeper wrote:
AngelEye88NL wrote:
I have the same problem. I made a report with the computer name, date of last seen, IP address, total amount of memory and the CPU, but I get a giant report with alot of double results.

Distinct is already added. The report:

Quote:
Select Distinct Top 1000000 tblcomputers.ComputerUnique, tblcomputers.Computername, tblcomputers.LastknownIP, tblcomputers.Lastseen, web40CorrectMemory.Memory, web40ProcessorCapacity.CPU From tblcomputers, web40CorrectMemory, web40ProcessorCapacity Where tblcomputers.LastknownIP Like '192.168.30.%'


Please try the report below. You did not link your tables to tblComputers, which is necessary.

When linking tables to tblComputers you will be most commonly linking the “Computername” fields in both tables. You do this by “grabbing” (left-click and hold) the Computername field from one table and dragging it to the Computername name field of the other table. Let go of your left mouse button to see the connection appear. Right-click on a connection and choose Remove to delete it.

Quote:
Select Distinct Top 1000000 tblComputers.ComputerUnique,
tblComputers.Computername, tblComputers.LastknownIP, tblComputers.Lastseen,
web40CorrectMemory.Memory, web40ProcessorCapacity.CPU
From tblComputers Inner Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Inner Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Where tblComputers.LastknownIP Like '192.168.30.%'


Worked! Thanks very much. :)
Back to top
Users browsing this topic
Guest
Untag as favorite
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Contact: E-mail Lansweeper - Skype : Lansweeper
Copyright 2004 - 2011 © Hemoco bvba