Need Help Hiding Certain Assets In Report

[PapaTuck]

Hi, I have a report to show assets not yet approved by our CM team. We have a custom field called Function that we have used to identify a device's purpose as well as note those that are not approved on our network.

When I try to filter out those marked "Not Approved On Network", other devices disappear from the report but those remain. I've used Not Like, <>, !=, etc., but can't find a solution to keep all assets except those marked "Not Approved...". Can anyone help?

Here's my code:

Select Distinct Top 1000000 tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssetCustom.Custom9 As Environment,
Left(tblAssets.AssetName, 50) As AssetName,
tblAssets.IPAddress As [IP Address],
tblAssets.Mac As [MAC Address],
tblAssetCustom.Custom1 As [Virtual Asset],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Firstseen As Created,
Left(Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease,
tblMacOSInfo.SystemVersion, tblAssets.Description), 60) As OS,
tblAssetCustom.Custom4 As [Function],
(Case
When tsysIPLocations.IPLocation Like '%Telework VPN' Then
tsysIPLocations.IPLocation
When aaa.AssetName Is Not Null And CharIndex('.ds', aaa.AssetName) > 0 Then
SubString(aaa.AssetName, 0, CharIndex('.ds', aaa.AssetName))
Else aaa.AssetName
End) As [Building Number],
tblAssetCustom.Custom2 As [Critical System],
Coalesce(tblAssetCustom.Custom19, Null, '') As [Approved Asset]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join (Select tblAssets.AssetID,
tblAssets_1.AssetName
From tblAssets
Inner Join tblAssetMacAddress On
tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
And tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID =
tblAssets_1.AssetID) As aaa On aaa.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename Not Like 'Monitor' And
tblAssetCustom.Custom4 <> 'Approved' And Coalesce(tblAssetCustom.Custom19,
Null, '') <> 'yes' And tblAssets.Assettype <> 66 And tblAssetCustom.State = 1
Order By [Asset Type]

[PapaTuck · Accepted solution]

We changed the Where clause and that solved the problem. Thanks to anyone who considered helping!

Where
tsysAssetTypes.AssetTypename Not Like 'Monitor' And
coalesce(tblAssetCustom.Custom4, Null, '') Not Like 'Not Approved On Network' And
Coalesce(tblAssetCustom.Custom19, Null, '') <> 'yes' And tblAssets.Assettype <> 66 And tblAssetCustom.State = 1
Order By [Asset Type]