→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
magic7502
Engaged Sweeper II
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?
5 REPLIES 5
magic7502
Engaged Sweeper II
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
Lansweeper Alumni
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
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
Can you please copy/paste your report sql code.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now