→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tsutton
Engaged Sweeper
Hi all,

I am creating a report based on numbers of PCs that are based on the operating system type, 32 or 64 bit and our internal build version.

However for some reasons, each records are coming up 4 times (all 4 of them are exactly the same) before showing the next record which is showing 4 times as well and so on. A example as shown in this screenshot:




Here's the report query I've used:

Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblRegistry.Value,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Userdomain,
tblAssets.Lastseen
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID,
tsysOS
Where tblRegistry.Value Like '%6.0' And tsysOS.OSname Like '%Win 7' And
tblComputersystem.SystemType Like 'x86%'


Why do they come up 4 times? What am I missing? How do I resolve this?

Many thanks,

Tony
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
You've added tsysOS, but you didn't link it, so you are multiplying your number of records in the report by the number of records in tsysOS this way.
Please try this first:

Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblRegistry.Value,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Userdomain,
tblAssets.Lastseen
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblRegistry.Value Like '%6.0' And tsysOS.OSname Like '%Win 7' And
tblComputersystem.SystemType Like 'x86%'

View solution in original post

2 REPLIES 2
Hemoco
Lansweeper Alumni
You've added tsysOS, but you didn't link it, so you are multiplying your number of records in the report by the number of records in tsysOS this way.
Please try this first:

Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblRegistry.Value,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Userdomain,
tblAssets.Lastseen
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblRegistry.Value Like '%6.0' And tsysOS.OSname Like '%Win 7' And
tblComputersystem.SystemType Like 'x86%'
tsutton
Engaged Sweeper
Lansweeper wrote:
You've added tsysOS, but you didn't link it, so you are multiplying your number of records in the report by the number of records in tsysOS this way.


How did I miss that?

That fixed it - thanks!