→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lukee20
Engaged Sweeper
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%')
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Could you try the following report instead:

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%')

View solution in original post

4 REPLIES 4
AngelEye88NL
Engaged Sweeper
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:

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.%'
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:

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.

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. 🙂
Hemoco
Lansweeper Alumni
Could you try the following report instead:

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%')