Can't get like/not like syntax to work in reports

[AndrewP]

I'm trying to use the following clause in a report

Where tblAssets.IPAddress Not Like '10.2.%'

or

Where tblAssets.IPAddress Like '10.2.%'

Every time I do, I save it, run the report, it shows nothing, so I go back in to edit it, and I see that it got changed to

Where tblAssets.IPAddress = 'not like ''10.2.%'

and

Where tblAssets.IPAddress = 'like ''10.2.%'

respectively

[Tom_P]

We've added the requested line to the SQL but found no errors. You can find the SQL statement we used below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Custom1 As asset_tag
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
Where 
  tsysIPLocations.IPLocation = 'MJct Subnet All' And 
  tblAssets.IPAddress Not Like '10.2.%' And
  tblAssetCustom.State = 1 And 
  tblAssetCustom.Model <> 'Virtual Machine'

[AndrewP]

Nothing sensitive, this is the report without that line

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Custom1 As asset_tag
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
Where tsysIPLocations.IPLocation = 'MJct Subnet All' And tblAssetCustom.State =
  1 And tblAssetCustom.Model <> 'Virtual Machine'

That runs fine, only when I try to add the additional condition do I run into the issue above.

[Tom_P]

When using the SQL 'Like' statement, you can indeed use wildcard to make partial matches. This is however general SQL behavior and not specific to Lansweeper. More information about this statement can be found here. As for the original issue, we were not able to reproduce this behavior. When adding the following to the query, the expected results were returned:

Using the SQL statement itself:

where IPAddress not like '10.2%'

Using the editor and adding this to the IPAddress 'criteria'-field:

Not Like '10.2.%'

As you mention that the query is being altered by the report builder, this could indicate that there is another SQL error in the statement which 'breaks' the SQL statement. If you could provide the full SQL statement (or send it to support@lansweeper.com), we can troubleshoot this further.

[AndrewP]

My problem isn't that it's failing. The problem is that the software is changing the query I wrote to something that's invalid, and then the invalid one is failing.

I'm writing something like the below

Where tblAssets.IPAddress Not Like '10.2.%'

But it CHANGES it to this

Where tblAssets.IPAddress = 'Not Like ''10.2.%'

[gsvarney]

One of my techs suggested wildcard. I used Where tblAssets.AssetName Like '%rsd%'. Assuming yours start with the value you are using in your statement and end with anything, I don't know why yours are failing.

[gsvarney]

I am having this same problem right now. One of the columns in my report is "AssetName". When I run the report without a WHERE statement I see all my computers. When I add Where tblAssets.AssetName Like 'rsd' to the script I see NONE of my computers. I did just try entering a complete computer name and got expected results. I thought the point of LIKE was to make partial matches. What am I (are we) missing?