→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ponchohobono
Champion Sweeper
The WHERE clause in the below code is not working. What am I missing?

SELECT tblAssetCustom.Location As Location,
tsysAssetTypes.AssetTypename As Type,
tblAssetCustom.BarCode As AssetID,
TsysChassisTypes.ChassisName As Chassis,
/* TsysChassisTypes.Chassistype As TypeID, */
tblAssets.AssetName As Name,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model As Model,
tblAssetCustom.Serialnumber As SerialNumber,
tblAssets.IPAddress As IPAddress,
tblAssets.LastActiveScan As LastSeen
/* tblAssets.AssetID As ID */
FROM tblAssets
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblSystemEnclosure On tblSystemEnclosure.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
WHERE tsysAssetTypes.AssetTypename <> 'Location'
OR TsysChassisTypes.Chassistype <> 12
OR TsysChassisTypes.Chassistype IS NULL
ORDER BY tblAssetCustom.Location, tsysAssetTypes.AssetTypename, TsysChassisTypes.ChassisName
Thanks, Patrick http://patrickhoban.wordpress.com
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
We assume you meant to use the AND operator and not the OR operator. You can try the query below instead. If this doesn't return what you're after, could you clarify what you are trying to list exactly?

For future reporting, we would recommend reviewing some SQL tutorials first, as the Lansweeper report builder uses standard SQL queries. If you know how to build SQL queries, then you know how to build Lansweeper reports as well. There are several good tutorials available online, e.g.: http://www.w3schools.com/sql/default.asp

Select Top 1000000 tblAssetCustom.Location As Location,
tsysAssetTypes.AssetTypename As Type,
tblAssetCustom.BarCode As AssetID,
TsysChassisTypes.ChassisName As Chassis,
tblAssets.AssetName As Name,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model As Model,
tblAssetCustom.Serialnumber As SerialNumber,
tblAssets.IPAddress As IPAddress,
tblAssets.LastActiveScan As LastSeen
From tblAssets
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblSystemEnclosure On tblSystemEnclosure.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tsysAssetTypes.AssetTypename <> 'Location' And
((TsysChassisTypes.Chassistype <> 12) Or (TsysChassisTypes.Chassistype Is
Null))
Order By Location,
Type,
Chassis

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
We assume you meant to use the AND operator and not the OR operator. You can try the query below instead. If this doesn't return what you're after, could you clarify what you are trying to list exactly?

For future reporting, we would recommend reviewing some SQL tutorials first, as the Lansweeper report builder uses standard SQL queries. If you know how to build SQL queries, then you know how to build Lansweeper reports as well. There are several good tutorials available online, e.g.: http://www.w3schools.com/sql/default.asp

Select Top 1000000 tblAssetCustom.Location As Location,
tsysAssetTypes.AssetTypename As Type,
tblAssetCustom.BarCode As AssetID,
TsysChassisTypes.ChassisName As Chassis,
tblAssets.AssetName As Name,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model As Model,
tblAssetCustom.Serialnumber As SerialNumber,
tblAssets.IPAddress As IPAddress,
tblAssets.LastActiveScan As LastSeen
From tblAssets
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblSystemEnclosure On tblSystemEnclosure.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tsysAssetTypes.AssetTypename <> 'Location' And
((TsysChassisTypes.Chassistype <> 12) Or (TsysChassisTypes.Chassistype Is
Null))
Order By Location,
Type,
Chassis