Report based on Default Gateway

[poweld1]

At the moment i've got a report as below.

Select Distinct Top 1000000 co1.Computername, co1.Computer, co1.Username, cs1.Manufacturer, cs1.Model, csp1.IdentifyingNumber As [Serial Number], cpu.Name As [Processor Model], cpu.MaxClockSpeed As [CPU (MHz)], cs1.NumberOfProcessors As Cores, os1.TotalVisibleMemorySize / 1024 As [Memory  (MB)], os1.Caption As [Operating System], os1.CSDVersion As [Service Pack], co1.LastknownIP As [IP Address], co1.Lastseen As [Last Seen] From tblComputers co1 Left Outer Join tblOperatingSystem os1 On (co1.Computername = os1.Computername) Left Outer Join tblComputerSystemProduct csp1 On (co1.Computername = csp1.Computername) Left Outer Join tblComputerSystem cs1 On (co1.Computername = cs1.Computername) Left Outer Join tblDisplayControllerConfiguration dcc On (co1.Computername = dcc.Computername) Left Outer Join tblProcessor cpu On (co1.Computername = cpu.Computername) Left Outer Join tblDiskDrives dsk On (co1.Computername = dsk.Computername) Inner Join tblNetwork On co1.Computername = tblNetwork.Computername Where dsk.Caption Like 'C:' And dsk.Description Like '%fixed disk%' And tblNetwork.DefaultIPGateway = '10.70.1.254' Or tblNetwork.DefaultIPGateway = '10.71.1.254'

Is it possible to change wildcard on the third octet of the Default IP Gateway e.g. 10.70.x.254 and 10.71.x.254

[Esben_D]

Since this is a very old report it is still using old database tables which have been renamed and/or changed.

The following tables (from the current build) are used:

• The default SQL (in the report builder)
  
• tblProcessor
  
• tblPhysicalMemory
  
• tblOperatingsystem
  
• tblDiskDrives
  
• tblNetwork

If you are just interested in the Default IP Gateway you can still use the same % wildcard.

[BES_IT]

When I try and run this report I get the following error:

Invalid SELECT statement. Unknown object name: "co1".: Unexpected token "co1" at line 0, column -1

Is there a newer version of this?

[Hemoco · Accepted solution]

You can try this

Select Distinct Top 1000000 co1.Computername, co1.Computer, co1.Username, cs1.Manufacturer, cs1.Model, csp1.IdentifyingNumber As [Serial Number], cpu.Name As [Processor Model], cpu.MaxClockSpeed As [CPU (MHz)], cs1.NumberOfProcessors As Cores, os1.TotalVisibleMemorySize / 1024 As [Memory (MB)], os1.Caption As [Operating System], os1.CSDVersion As [Service Pack], co1.LastknownIP As [IP Address], co1.Lastseen As [Last Seen] From tblComputers co1 Left Outer Join tblOperatingSystem os1 On (co1.Computername = os1.Computername) Left Outer Join tblComputerSystemProduct csp1 On (co1.Computername = csp1.Computername) Left Outer Join tblComputerSystem cs1 On (co1.Computername = cs1.Computername) Left Outer Join tblDisplayControllerConfiguration dcc On (co1.Computername = dcc.Computername) Left Outer Join tblProcessor cpu On (co1.Computername = cpu.Computername) Left Outer Join tblDiskDrives dsk On (co1.Computername = dsk.Computername) Inner Join tblNetwork On co1.Computername = tblNetwork.Computername Where dsk.Caption Like 'C:' And dsk.Description Like '%fixed disk%' And tblNetwork.DefaultIPGateway like '10.70.%.254' Or tblNetwork.DefaultIPGateway like '10.71.%.254'