→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AndrewP
Engaged Sweeper
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
6 REPLIES 6
Tom_P
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper
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
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper
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
Engaged Sweeper
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
Engaged Sweeper
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?