New Reort builder

[willetzky]

I have created a report but the web report builder changes the sql when saving the report breaking the query. How can i manually import the sql query without it being changed? It breaks the join in the not select


Working query

Select Top 1000000 upgrade_web40osname.Compimage As icon,
upgrade_tblcomputers.Computername,
upgrade_tblcomputers.ComputerUnique,
upgrade_web40alliplocations.IPLocation,
upgrade_tblcomputers.LastknownIP,
upgrade_tblcomputers.Username,
upgrade_tblcomputers.Lastseen,
upgrade_tblSoftware.softwareName,
upgrade_tblSoftware.softwareVersion

From upgrade_tblcomputers
Inner Join upgrade_web40activecomputers On upgrade_tblcomputers.Computername =
upgrade_web40activecomputers.Computername
Inner Join upgrade_web40osname On upgrade_web40osname.Computername =
upgrade_tblcomputers.Computername
Inner Join upgrade_tbloperatingsystem On upgrade_tblcomputers.Computername =
upgrade_tbloperatingsystem.Computername
Inner Join upgrade_tblcomputersystem On upgrade_tblcomputers.Computername =
upgrade_tblcomputersystem.Computername
Right Join upgrade_web40alliplocations
On upgrade_web40alliplocations.Computername =
upgrade_tblcomputers.Computername
Left Join .upgrade_tblSoftware On .upgrade_tblcomputers.Computername =
.upgrade_tblsoftware.ComputerName
Where
upgrade_tblcomputers.Computername Not In (Select
.upgrade_tblsoftware.ComputerName
From upgrade_tblsoftware Inner Join .upgrade_tblcomputers On .upgrade_tblcomputers.Computername =
.upgrade_tblsoftware.ComputerName
Where .upgrade_tblsoftware.softwareVersion Like '3.4.5619.10914') And
upgrade_tblcomputersystem.Domainrole < 2 And .upgrade_tblSoftware.softwareName like 'Safend%'
Order By upgrade_tblcomputers.Computer

How it changes it

Select Top 1000000 upgrade_Web40OSName.Compimage As icon,
upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_web40AllIPLocations.IPLocation,
upgrade_tblComputers.LastknownIP,
upgrade_tblComputers.Username,
upgrade_tblComputers.Lastseen,
upgrade_tblSoftware.softwareName,
upgrade_tblSoftware.softwareVersion
From upgrade_tblComputers
Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername
Inner Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
upgrade_tblComputers.Computername
Inner Join upgrade_tblOperatingsystem On upgrade_tblComputers.Computername =
upgrade_tblOperatingsystem.Computername
Inner Join upgrade_tblComputersystem On upgrade_tblComputers.Computername =
upgrade_tblComputersystem.Computername
Right Join upgrade_web40AllIPLocations
On upgrade_web40AllIPLocations.Computername =
upgrade_tblComputers.Computername
Left Join upgrade_tblSoftware On .upgrade_tblcomputers.Computername =
upgrade_tblSoftware.ComputerName
Where
upgrade_tblComputers.Computername Not In (Select
.upgrade_tblSoftware.ComputerName
From Inner Join On .upgrade_tblComputers.Computername =
.upgrade_tblSoftware.ComputerName
Where .upgrade_tblSoftware.softwareVersion Like '3.4.5619.10914') And
upgrade_tblSoftware.softwareName Like 'Safend%' And
upgrade_tblComputersystem.Domainrole < 2
Order By upgrade_tblComputers.Computer

[willetzky]

Thanks after the update this fixed the issue

[Hemoco]

Did you update your Lansweeper installation as instructed?

[willetzky]

Hi

It still changes and breaks the where part of the query when trying to save the report

This is what it automatically changes the section to

Where
upgrade_tblComputers.Computername Not In (Select
.upgrade_tblsoftware.ComputerName
From Inner Join On .upgrade_tblcomputers.Computername =
.upgrade_tblsoftware.ComputerName
Where .upgrade_tblsoftware.softwareVersion Like '3.4.5619.10914') And
upgrade_tblSoftware.softwareName Like 'Safend%' And
upgrade_tblComputersystem.Domainrole < 2
Order By upgrade_tblComputers.Computer

removing the tables from the join and adding periods

[Hemoco]

There are already unwanted periods in your initial query. Please do the following:
- Update to this Lansweeper version.
- Try running the report as shown below.

Select Top 1000000 upgrade_web40osname.Compimage As icon,
upgrade_tblcomputers.Computername,
upgrade_tblcomputers.ComputerUnique,
upgrade_web40alliplocations.IPLocation,
upgrade_tblcomputers.LastknownIP,
upgrade_tblcomputers.Username,
upgrade_tblcomputers.Lastseen,
upgrade_tblSoftware.softwareName,
upgrade_tblSoftware.softwareVersion
From upgrade_tblcomputers
Inner Join upgrade_web40activecomputers On upgrade_tblcomputers.Computername =
upgrade_web40activecomputers.Computername
Inner Join upgrade_web40osname On upgrade_web40osname.Computername =
upgrade_tblcomputers.Computername
Inner Join upgrade_tbloperatingsystem On upgrade_tblcomputers.Computername =
upgrade_tbloperatingsystem.Computername
Inner Join upgrade_tblcomputersystem On upgrade_tblcomputers.Computername =
upgrade_tblcomputersystem.Computername
Right Join upgrade_web40alliplocations
On upgrade_web40alliplocations.Computername =
upgrade_tblcomputers.Computername
Left Join upgrade_tblSoftware On upgrade_tblcomputers.Computername =
upgrade_tblsoftware.ComputerName
Where
upgrade_tblcomputers.Computername Not In (Select
upgrade_tblsoftware.ComputerName
From upgrade_tblsoftware Inner Join upgrade_tblcomputers On upgrade_tblcomputers.Computername =
upgrade_tblsoftware.ComputerName
Where upgrade_tblsoftware.softwareVersion Like '3.4.5619.10914') And
upgrade_tblcomputersystem.Domainrole < 2 And upgrade_tblSoftware.softwareName like 'Safend%'
Order By upgrade_tblcomputers.Computer