cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jeff_Henze
Engaged Sweeper III
I'm a bit baffled by the fact that even though I specifically say I do not want to see records with a tblAssetCustom.Custom2 field like %retired%, I still get items with "Retired" in that field.

Here's the report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where (tblAssetCustom.Custom3 Not Like '%companyx%' Or
tblAssetCustom.Custom3 Is Null) And (tblAssetCustom.Custom2 Not Like
'%retired%' Or tblAssetCustom.Custom2 Not Like '%secondary%' Or
tblAssetCustom.Custom2 Is Null) And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate


That's not the only anomaly with my report, but I believe it's the most telling of what I'm doing wrong. I assume this is something obvious to a SQL-er, but I'm not seeing it.

Thanks,
-Jeff
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
I've adjusted your reports to replace some OR's with AND's, if you want to your report to exclude multiple values, using OR will not work, as illustrated above. I wasn't sure if you also wanted to exclude NULL values, or if you wanted to see NULL values. The report below currently filters out NULL values, if you wish to see them, you can remove the highlighted portion of the code.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%' And
tblAssetCustom.Custom3 Is Not Null
And tblAssetCustom.Custom2 Not Like
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%' And
tblAssetCustom.Custom2 Is Not Null
And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate

View solution in original post

3 REPLIES 3
Jeff_Henze
Engaged Sweeper III
Thanks guys, that was indeed the problem with my query and the solution worked well. It was too many AND ORs for my brain, but incredibly obvious now that you spelled it out.

Thanks,
-Jeff
Bruce_B
Lansweeper Alumni
I've adjusted your reports to replace some OR's with AND's, if you want to your report to exclude multiple values, using OR will not work, as illustrated above. I wasn't sure if you also wanted to exclude NULL values, or if you wanted to see NULL values. The report below currently filters out NULL values, if you wish to see them, you can remove the highlighted portion of the code.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%' And
tblAssetCustom.Custom3 Is Not Null
And tblAssetCustom.Custom2 Not Like
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%' And
tblAssetCustom.Custom2 Is Not Null
And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
RCorbeil
Honored Sweeper II
Looking at your WHERE clause
WHERE
( tblAssetCustom.Custom3 Not Like '%companyx%'
OR tblAssetCustom.Custom3 Is Null
)
AND ( tblAssetCustom.Custom2 Not Like '%retired%'
OR tblAssetCustom.Custom2 Not Like '%secondary%'
OR tblAssetCustom.Custom2 Is Null
)
AND ( tblAssetCustom.Manufacturer Like '%dell%'
OR tblAssetCustom.Manufacturer Like '%microsoft%'
)
AND tblAssetCustom.Model <> 'Virtual Machine'
AND tblADComputers.OU Not Like '%denver%'
AND tblComputersystem.Domainrole < 2

if Custom2 is, say, "Retired", then
(    tblAssetCustom.Custom2 Not Like '%retired%'      => false
OR tblAssetCustom.Custom2 Not Like '%secondary%' => true
OR tblAssetCustom.Custom2 Is Null => false
)

Since your condition is "If A or B or C", because B is true the block evaluates as true.

Assuming what you're after is filtering out a record if Custom2 contains either "retired" or "secondary", you might want to reformat that something like
  AND (
tblAssetCustom.Custom2 Is Null
OR ( tblAssetCustom.Custom2 Not Like '%retired%'
AND tblAssetCustom.Custom2 Not Like '%secondary%'
)
)

New to Lansweeper?

Try Lansweeper For Free

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

Try Now