Notification

Icon
Error

NOT doesn't work as expected

Posted: Wednesday, June 3, 2020 7:15:11 PM(UTC)
ABECU

ABECU

Member Original PosterPosts: 18
0
Like
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.
KevinA-REJIS
#1KevinA-REJIS Member Posts: 28  
posted: 6/3/2020 9:14:57 PM(UTC)
You could try "NOT LIKE '%modelname%'".

Can you post the code of one of the reports? It might help with troubleshooting.
ABECU
#2ABECU Member Original PosterPosts: 18  
posted: 6/5/2020 3:35:31 PM(UTC)
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
#3KevinA-REJIS Member Posts: 28  
posted: 6/5/2020 5:49:29 PM(UTC)
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.

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


Give that a shot.

Active Discussions

Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Today at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Today at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: Yesterday at 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)
Lansweeper Wake on Lan in VLANs
by  FrankSc   Go to last post Go to first unread
Last post: 7/3/2020 1:56:46 PM(UTC)
Lansweeper Ticket Closed = email to all helpdesk desk agents
by  Laurent Maene  
Go to last post Go to first unread
Last post: 7/3/2020 1:23:49 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  pryan67   Go to last post Go to first unread
Last post: 7/3/2020 1:12:17 PM(UTC)
Lansweeper Office 365 32bit vs 64bit?
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/2/2020 10:35:19 PM(UTC)