cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ABECU
Engaged Sweeper III
I'm not an SQL expert. I have some reports that are working, but I'd like to exclude certain PC models from them. If I use "NOT LIKE 'modelname'", instead of showing me every PC that matches the rest of the report and isn't that model name, I get every PC in the database that isn't that model name. Doing "like 'modelname'" does just show PCs of that model that match the report as expected. I assuming I'm missing something simple? Thanks.
3 REPLIES 3
KevinA-REJIS
Champion Sweeper II
I put the report in our Lansweeper (modified it for McAfee), and it returned some virtual servers. I added a NOT LIKE line in the below section for '%VMware%' and that removed them.

Where Case
When soft01.AssetID Is Null Then 'NO'
Else 'YES'
End Like 'no' And tblAssetCustom.Model Not Like '%modelname%' And tblAssetCustom.State = 1


Give that a shot.
ABECU
Engaged Sweeper III
That's the problem, as soon as I add any NOT LIKE to the modelname row it stops matching anything else in the query. I've had this problem on many reports so I'm really not sure what to do. This query works perfectly, it shows me all machines without SEP installed, but as soon as I add "Not Like 'hp t%'" it goes off the rails, and also adding "Not Like 'hp mt&'" it becomes completely wrong. I assume the GUI builder is adding these to the wrong place in the query but I don't know enough SQL to guess how.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When soft01.AssetID Is Null Then 'NO'
Else 'YES'
End As SEP,
tblAssets.Firstseen,
tblAssets.Lastseen,
Case
When soft01.AssetID Is Null Then '#ffcccc'
End As backgroundcolor,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Symantec Endpoint Protection%')
As soft01 On soft01.AssetID = tblAssets.AssetID
Where Case
When soft01.AssetID Is Null Then 'NO'
Else 'YES'
End Like 'no' And tblAssetCustom.State = 1
Order By SEP,
tblAssetCustom.Model
KevinA-REJIS
Champion Sweeper II
You could try "NOT LIKE '%modelname%'".

Can you post the code of one of the reports? It might help with troubleshooting.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now