Custom Report - duplicate results

[magic7502]

Hello,
I have a problem with creating custom reports. I wanted to show all workstations on my network with Mcafee AV installed, so I copied the "Workstations: All AV" report, and changed the criteria from "like tsysantivirus.software" to "like '%mcafee%'"
the only problem is, there are duplicate results for each system. This results in over 20k results when I generate the report.

Please see the image below for more details. How can I fix this?

[magic7502]

I keep getting errors when I try to use what you gave me. I copy and paste what you put in your quote and I get this error:
ORDER BY items must appear in the select lsit if SELECT DISTINCT is specified.

and if I simply remove the 2nd tsysantivirus command from my original query, it keeps telling me that the "unexpected token WHERE appears".

Could you help me?
Also, are these just standard SQL queries? if not, where can I go to learn about how to use lansweepers custom report builder?

[Hemoco]

magic7502 wrote:
I keep getting errors when I try to use what you gave me. I copy and paste what you put in your quote and I get this error:
ORDER BY items must appear in the select lsit if SELECT DISTINCT is specified.

Could you run the report as shown below instead.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain, tblComputers.Lastseen,
Web40OSName.Compimage As icon, tblSoftware.softwareName As Software,
tblSoftware.softwareVersion As Version
From tblComputers Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername
Where tblSoftware.softwareName Like '%mcafee%' And
tblComputersystem.Domainrole < 2
Order By tblComputers.ComputerUnique

magic7502 wrote:
Also, are these just standard SQL queries? if not, where can I go to learn about how to use lansweepers custom report builder?

Yes, these are standard SQL queries and can be executed directly in SQL Server Management Studio as well. An example on how to build custom reports can be found on page 82 and beyond of our online documentation.

[Hemoco]

Please run the report as shown below instead. Tsysantivirus was not needed in your query.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain, tblComputers.Lastseen,
Web40OSName.Compimage As icon, tblSoftware.softwareName As Software,
tblSoftware.softwareVersion As Version
From tblComputers Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername
Where tblSoftware.softwareName Like '%mcafee%' And
tblComputersystem.Domainrole < 2
Order By dbo.tblComputers.Computer

[magic7502]

Sure, here it is.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, tblComputers.Lastseen, Web40OSName.Compimage As icon, tblSoftware.softwareName As Software, tblSoftware.softwareVersion As Version From tblComputers Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join tblComputersystem On tblComputers.Computername = tblComputersystem.Computername, tsysantivirus Where tblSoftware.softwareName Like '%mcafee%' And tblComputersystem.Domainrole < 2 Order By dbo.tblComputers.Computer

[Hemoco]

Can you please copy/paste your report sql code.