→ 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: 
roro
Engaged Sweeper
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".
1 ACCEPTED SOLUTION
roro
Engaged Sweeper
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%')

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Lansweeper Tech Support
Lansweeper Tech Support
Hi roro,

I moved your post to the report questions as you will be more likely to get an answer there.
roro
Engaged Sweeper
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%')