cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ponchohobono
Champion Sweeper
When I run the query below I get a few computers duplcated. It appears that there are multiple entries for a few computers in the tblSystemEnclosure table. I'd also like to get the OS in there as well. Any suggestions?

SELECT tblCompCustom.Location,
TsysChassisTypes.ChassisName as Type,
tblcomputers.Computer,
tblComputerSystemProduct.Vendor as Vendor,
tblComputerSystemProduct.Name as Model,
tblComputerSystemProduct.IdentifyingNumber as SerialNumber,
tblcomputers.LastknownIP as IPAddress,
tblcomputers.LastActiveScan
FROM tblcomputers
LEFT JOIN tblCompCustom ON tblCompCustom.Computername = tblcomputers.Computername
LEFT JOIN tblSystemEnclosure ON tblSystemEnclosure.Computername = tblcomputers.Computername
LEFT JOIN tblComputerSystemProduct ON tblComputerSystemProduct.Computername = tblcomputers.Computername
LEFT JOIN TsysChassisTypes ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
ORDER BY tblcomputers.Computer
Thanks, Patrick http://patrickhoban.wordpress.com
1 ACCEPTED SOLUTION
Ponchohobono
Champion Sweeper
Ahhh. This worked for me.

Select tblCompCustom.Location As Location,
TsysChassisTypes.ChassisName As Type,
tblcomputers.Computer As Name,
Web40OSName.OSname As OS,
Web40OSName.SP As SP,
tblComputerSystemProduct.Vendor As Vendor,
tblComputerSystemProduct.Name As Model,
tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
tblcomputers.LastknownIP As IPAddress,
tblcomputers.LastActiveScan As LastSeen,
tblcomputers.Computername
From tblcomputers
Left Join tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join tblSystemEnclosure On tblSystemEnclosure.Computername = tblcomputers.Computername
Left Join tblComputerSystemProduct On tblComputerSystemProduct.Computername = tblcomputers.Computername
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
UNION
SELECT tblCustDevices.Location As Location,
tsysDevicetypes.ItemTypename As Type,
tblCustDevices.Displayname As Name,
tblCustDevices.Description As OS,
CAST('' As int) As SP,
tblCustDevices.Vendor As Vendor,
tblCustDevices.Model As Model,
tblCustDevices.Serialnumber As SerialNumber,
tblCustDevices.Ipaddress As IPAddress,
tblCustDevices.LastSeen As LastSeen,
CAST('' As int) As Computername
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
Order By Name
Thanks, Patrick http://patrickhoban.wordpress.com

View solution in original post

4 REPLIES 4
Ponchohobono
Champion Sweeper
Ahhh. This worked for me.

Select tblCompCustom.Location As Location,
TsysChassisTypes.ChassisName As Type,
tblcomputers.Computer As Name,
Web40OSName.OSname As OS,
Web40OSName.SP As SP,
tblComputerSystemProduct.Vendor As Vendor,
tblComputerSystemProduct.Name As Model,
tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
tblcomputers.LastknownIP As IPAddress,
tblcomputers.LastActiveScan As LastSeen,
tblcomputers.Computername
From tblcomputers
Left Join tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join tblSystemEnclosure On tblSystemEnclosure.Computername = tblcomputers.Computername
Left Join tblComputerSystemProduct On tblComputerSystemProduct.Computername = tblcomputers.Computername
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
UNION
SELECT tblCustDevices.Location As Location,
tsysDevicetypes.ItemTypename As Type,
tblCustDevices.Displayname As Name,
tblCustDevices.Description As OS,
CAST('' As int) As SP,
tblCustDevices.Vendor As Vendor,
tblCustDevices.Model As Model,
tblCustDevices.Serialnumber As SerialNumber,
tblCustDevices.Ipaddress As IPAddress,
tblCustDevices.LastSeen As LastSeen,
CAST('' As int) As Computername
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
Order By Name
Thanks, Patrick http://patrickhoban.wordpress.com
Hemoco
Lansweeper Alumni
Fields that are numeric in the first query must also be numeric in the 2e Query (computername, sp, ...)

Make them numeric in the 2e query or varchar in the first.
Ponchohobono
Champion Sweeper
That worked thanks. One more tweak. I want to use a UNION to also return devices. Using the two queries seperatly they work but using a UNION I get an error that says, "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric." What am I doing wrong?

Select tblCompCustom.Location As Location,
TsysChassisTypes.ChassisName As Type,
tblcomputers.Computer As Name,
Web40OSName.OSname As OS,
Web40OSName.SP As SP,
tblComputerSystemProduct.Vendor As Vendor,
tblComputerSystemProduct.Name As Model,
tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
tblcomputers.LastknownIP As IPAddress,
tblcomputers.LastActiveScan As LastSeen,
tblcomputers.Computername
From tblcomputers
Left Join tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join tblSystemEnclosure On tblSystemEnclosure.Computername = tblcomputers.Computername
Left Join tblComputerSystemProduct On tblComputerSystemProduct.Computername = tblcomputers.Computername
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Inner Join Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
UNION
SELECT tblCustDevices.Location As Location,
tsysDevicetypes.ItemTypename As Type,
tblCustDevices.Displayname As Name,
tblCustDevices.Description As OS,
'' As SP,
tblCustDevices.Vendor As Vendor,
tblCustDevices.Model As Model,
tblCustDevices.Serialnumber As SerialNumber,
tblCustDevices.Ipaddress As IPAddress,
tblCustDevices.LastSeen As LastSeen,
'' As Computername
FROM tblCustDevices, tsysDevicetypes
WHERE tblCustDevices.Devicetype = tsysDevicetypes.ItemType
Order By Name
Thanks, Patrick http://patrickhoban.wordpress.com
Hemoco
Lansweeper Alumni
try this:
I've filtered out the docking stations.

Select tblCompCustom.Location, TsysChassisTypes.ChassisName As Type,
tblcomputers.Computer, Web40OSName.OSname, Web40OSName.SP,
tblComputerSystemProduct.Vendor As Vendor, tblComputerSystemProduct.Name As
Model, tblComputerSystemProduct.IdentifyingNumber As SerialNumber,
tblcomputers.LastknownIP As IPAddress, tblcomputers.LastActiveScan,
tblcomputers.Computername
From tblcomputers Left Join
tblCompCustom On tblCompCustom.Computername = tblcomputers.Computername
Left Join
tblSystemEnclosure On tblSystemEnclosure.Computername =
tblcomputers.Computername Left Join
tblComputerSystemProduct On tblComputerSystemProduct.Computername =
tblcomputers.Computername Left Join
TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype Inner Join
Web40OSName On Web40OSName.Computername = tblcomputers.Computername
Where TsysChassisTypes.Chassistype <> 12
Order By tblcomputers.Computer

New to Lansweeper?

Try Lansweeper For Free

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

Try Now