Lansweeper report to match computer name

[roro]

Here is my SQL

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblOperatingsystem.Caption As FullOSname,
tblAssets.Version As [Windows Version]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.AssetName Like 'LT-FR' And tsysOS.OSname = 'win 10' And
tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like 'PC-FR')

I'm trying to match only computers that start with "LT-FR" or "PC-FR".

[RCorbeil]

You may want to confirm your WHERE clause.

Where
  ( tblAssets.AssetName Like '%LT-FR%'
    And tsysOS.OSname = 'win 10'
    And tblAssetCustom.State = 1)
  Or
  (tblAssets.AssetName Like '%PC-FR%')

You're saying

  (asset name contains "LT-FR"
    AND OS is Win10
    AND asset state is active
  )
  OR
  (asset name contains "PC-FR"
    AND don't care what OS
    AND any state (active, non-active, sold, broken, etc.)
  )

If you mean that you want active Win10 machines that meet the two name conditions, you'll want to change that to

Where
  tblAssetCustom.State = 1
  And tsysOS.OSname = 'win 10'
  And ( tblAssets.AssetName Like '%LT-FR%'
        Or tblAssets.AssetName Like '%PC-FR%')

[ErikT]

Hi roro,

I moved your post to the report questions as you will be more likely to get an answer there.

[roro · Accepted solution]

Erik.T wrote:
Hi roro,

I moved your post to the report questions as you will be more likely to get an answer there.

I actually solved this issue myself.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblOperatingsystem.Caption As FullOSname,
tblAssets.Version As [Windows Version]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.AssetName Like '%LT-FR%' And tsysOS.OSname = 'win 10'
And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like '%PC-FR%')