cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ponchohobono
Champion Sweeper
When I run the query below it is missing most but not all of the computer that haven't been seen in 30 days. Any idea why?

SELECT '' as Location,
TsysChassisTypes.ChassisName as Type,
tblcomputers.Computer as Name,
tblComputerSystemProduct.Vendor as Vendor,
tblComputerSystemProduct.Name as Model,
tblComputerSystemProduct.IdentifyingNumber as SerialNumber,
tblcomputers.LastknownIP as IPAddress
FROM tblcomputers, tblComputerSystemProduct, tblSystemEnclosure, TsysChassisTypes
WHERE tblcomputers.Computername = tblComputerSystemProduct.Computername
AND tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
AND tblcomputers.Computername = tblSystemEnclosure.Computername
AND tblComputerSystemProduct.Vendor <> 'Vmware, Inc.'
AND TsysChassisTypes.ChassisName <> 'Docking Station'
UNION
SELECT tblCustDevices.Location,
tsysDevicetypes.ItemTypename,
tblCustDevices.Displayname,
tblCustDevices.Vendor,
tblCustDevices.Description,
tblCustDevices.Serialnumber,
tblCustDevices.Ipaddress
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
ORDER BY LastknownIP
Thanks, Patrick http://patrickhoban.wordpress.com
2 REPLIES 2
Hemoco
Lansweeper Alumni
I'm guessing that you performed an upgrade from 3.5 which didn't have systemenclosure.
Hemoco
Lansweeper Alumni
try this:

Select '' As Location, TsysChassisTypes.ChassisName As Type,
tblcomputers.Computer As Name, tblComputerSystemProduct.Vendor As Vendor,
tblComputerSystemProduct.Name As Model,
tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
tblcomputers.LastknownIP As IPAddress
From tblcomputers Inner Join
tblComputerSystemProduct On tblcomputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblSystemEnclosure On tblcomputers.Computername =
tblSystemEnclosure.Computername Inner Join
TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where TsysChassisTypes.ChassisName <> 'Docking Station' And
tblComputerSystemProduct.Vendor <> 'Vmware, Inc.'
UNION
SELECT tblCustDevices.Location,
tsysDevicetypes.ItemTypename,
tblCustDevices.Displayname,
tblCustDevices.Vendor,
tblCustDevices.Description,
tblCustDevices.Serialnumber,
tblCustDevices.Ipaddress
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
ORDER BY LastknownIP