Duplicate rows returned in report

[ssilvestri]

Hi,

Im am getting some duplicates rows returned for the following report I am using to display user and computer details where Adobe Acrobat is installed - any suggestions on how to fix this or otherwise improve the SQL code?

Thanks.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, tblComputers.Domain, Web40OSName.OSname, tblOperatingsystem.Caption, Web40OSName.SP, tblComputers.Username, tblADusers.Lastname, tblADusers.Firstname, web40CorrectMemory.Memory As Memory, Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As varchar) As [Disk size], web40ProcessorCapacity.NrOfProcessors, web40ProcessorCapacity.CPU, tblComputerSystemProduct.Name As Model, tblComputerSystemProduct.Vendor As Manufacturer, tblComputerSystemProduct.IdentifyingNumber As Serial, tblComputers.LastknownIP As [IP Address], web40AllIPLocations.IPLocation, tblComputers.Lastseen, tblOperatingsystem.SerialNumber, tblOperatingsystem.Version, tblSoftware.softwareName, tblSoftware.softwareVersion, tblSoftware.Installdate From tblComputers Inner Join tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Inner Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Inner Join tblADusers On tblComputers.Username = tblADusers.Username Inner Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where (tblSoftware.softwareName Like '%Adobe Acrobat%' And tblSoftware.softwareName Not Like '%Reader%' And tblSoftware.softwareName Not Like '%Connect%') And tblDiskdrives.Caption = 'c:' Order By dbo.tblComputers.Computername

[Hemoco]

This was answered through email on Wednesday. The corrected report can be found below.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, Web40OSName.Compimage As icon,
tblComputers.Domain, Web40OSName.OSname, tblOperatingsystem.Caption,
Web40OSName.SP, tblComputers.Username, tblADusers.Lastname,
tblADusers.Firstname, web40CorrectMemory.Memory As Memory,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As varchar) As [Disk size], web40ProcessorCapacity.NrOfProcessors,
web40ProcessorCapacity.CPU, tblComputerSystemProduct.Name As Model,
tblComputerSystemProduct.Vendor As Manufacturer,
tblComputerSystemProduct.IdentifyingNumber As Serial, tblComputers.LastknownIP
As [IP Address], web40AllIPLocations.IPLocation, tblComputers.Lastseen,
tblOperatingsystem.SerialNumber, tblOperatingsystem.Version
From tblComputers Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Left Outer Join
web40ProcessorCapacity On tblComputers.Computername =
web40ProcessorCapacity.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Where (tblSoftware.softwareName Like '%Adobe Acrobat%' And
tblSoftware.softwareName Not Like '%Reader%' And
tblSoftware.softwareName Not Like '%Connect%') And tblDiskdrives.Caption =
'c:'
Order By dbo.tblComputers.Computername

[ghelpdesk]

You could try the DISTINCT command after SELECT. That will merge all duplicates into a single row.

so:

Select DISTINCT Top 1000000 tblComputers.Computername...

But that kinda glosses over that the duplicated data might need to be addressed? I'm not familiar enough with the LS tables to know that answer.

[Hemoco]

Please contact us at support@lansweeper.com and provide us with the output of the report. For easy referencing, also include a link to this forum thread in your email.