cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tungmeister
Engaged Sweeper
I've got the report as below. I'm trying to exclude devices that contain 'LAPTOP' or 'TABLET' within their name however they're still showing up in the report - not sure what I'm missing.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
ProcCapacity.CPU,
ProcCapacity.MaxClockSpeed,
tsysOS.OSname,
tblAssetCustom.Location,
tsysOS.OSname As OS,
tblAssets.Username,
tblADComputers.Description As Description1
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) As ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Not Like '%TABLET%')
Order By ProcCapacity.MaxClockSpeed
2 REPLIES 2
RCorbeil
Honored Sweeper II
As you've presented it, you only care that the laptops are active. Is that the case? I.e. you want to filter out all tablets, regardless of their state and you want to filter out active laptops. Charles.X has assumed, and I'm going to assume the same, that you want to include active devices and you want to filter out laptops and tablets.

I'm going to offer a small twist on Charles.X's suggested
WHERE
tblAssetCustom.State = 1
AND ( tblAssets.AssetName NOT LIKE '%LAPTOP%'
AND tblAssets.AssetName NOT LIKE '%TABLET%')

Or, since the parentheses are redundant because everything is being ANDed,
WHERE
tblAssetCustom.State = 1
AND tblAssets.AssetName NOT LIKE '%LAPTOP%'
AND tblAssets.AssetName NOT LIKE '%TABLET%'

If you pull the NOT out, you can phrase it more in line with what it looks like you were thinking:
WHERE
tblAssetCustom.State = 1
AND NOT ( tblAssets.AssetName LIKE '%LAPTOP%'
OR tblAssets.AssetName LIKE '%TABLET%')
Esben_D
Lansweeper Employee
Lansweeper Employee
I think your where clause is not correct.

Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Not Like '%TABLET%')


You'll have to use an And instead of an Or for the two names, removing all assets which have laptop in the name And all assets which have tablet in the name.

Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssets.AssetName Not Like '%TABLET%') And tblAssetCustom.State = 1

New to Lansweeper?

Try Lansweeper For Free

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

Try Now