→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
IT-Administrati
Engaged Sweeper
Hey There,

we are trying to get a Report for all our Workstations and Notebooks of the Company.

All Notebooks have the "NB" in their Name and all Workstations have "WS" in it.

so theoreticaly i can filter with


Where tsysAssetTypes.AssetTypename Like '%WS%' And
tsysAssetTypes.AssetTypename Like '%NB%' And tblAssetCustom.State = 1


But if i add the Where i get no Results

So here is the Full SQL Statement


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.Lastchanged,
tblAssetCustom.SoftwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename Like '%WS%' And
tsysAssetTypes.AssetTypename Like '%NB%' And tblAssetCustom.State = 1


I Hope somebody can point me out what iam doing wrong
1 REPLY 1
RCorbeil
Honored Sweeper II
I'm going to pull out the old bag-of-gumballs analogy again.
Where
tsysAssetTypes.AssetTypename Like '%WS%'
And tsysAssetTypes.AssetTypename Like '%NB%'
And tblAssetCustom.State = 1

You've been handed a bag containing a variety of solid-coloured gumballs. You (in the role of the computer, testing conditions) pull out the gumballs, one at a time, and determine whether they meet your conditions. For analogy, I'm going to say the WS machines are red gumballs and the NB machines are green. The active state is superfluous to the analogy, so I'll ignore that.

You pull out the first gumball and test your conditions:
  • Is this gumball red?
  • AND is this gumball ALSO green?
If both conditions are true, select it for your final collection. If both conditions are not true, discard it.

Pull the next gumball and repeat until the bag is empty.

You hopefully see the problem: any given gumball with never be simultaneously red and green, so none will be selected.

Try adjusting your filter conditions:
Where
(tsysAssetTypes.AssetTypename Like '%WS%' OR tsysAssetTypes.AssetTypename Like '%NB%')
And tblAssetCustom.State = 1

This will select all active assets where the AssetTypename contains either WS or NB.

As with algebra, there is a precedence to logical operators, so you need to use parentheses to control the evaluation. Just as multiplication and division take precedence over addition and subtraction, AND takes precedence over OR. In the above, you presumably want to select anything that's active, so that's separated from the check for workstation or notebook name, the same as you would put parens around "A + B * C" if what you wanted was "(A + B) * C".