‎07-30-2019 05:53 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblADComputers.OU,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Lastseen,
tblADComputers.Description,
tblAssetCustom.Custom1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%tours%'
And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%blois%' And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Orleans%' And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And
tblADComputers.OU Like '%chatellerault%' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%poitiers%' And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%nogent%' And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chateaudun%'
And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%le mans%' And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Chartres%' And
tblAssetCustom.State = 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2)
Order By tblAssets.Domain,
tblAssets.AssetName
Solved! Go to Solution.
‎07-30-2019 10:36 PM
Where
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%tours%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%blois%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Orleans%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chatellerault%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%poitiers%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%nogent%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chateaudun%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%le mans%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Chartres%' And tblAssetCustom.State = 1 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole < 2
And tsysOS.OSname Not Like '%Win 10%'
And ( (tblADComputers.OU Like '%tours%')
Or (tblADComputers.OU Like '%blois%')
Or (tblADComputers.OU Like '%Orleans%')
Or (tblADComputers.OU Like '%chatellerault%')
Or (tblADComputers.OU Like '%poitiers%')
Or (tblADComputers.OU Like '%nogent%')
Or (tblADComputers.OU Like '%chateaudun%')
Or (tblADComputers.OU Like '%le mans%')
Or (tblADComputers.OU Like '%Chartres%')
)
OSnamereplacing
-----------
Not scanned
NT 3.51
NT 4
Win 10
Win 2000
Win 2000 S
Win 2003
Win 2003 R2
Win 2008
Win 2008 R2
Win 2012
Win 2012 R2
Win 2016
Win 2019
Win 7
Win 7 RC
Win 8
Win 8.1
Win Home
Win Vista
Win XP
And tsysOS.OSname Not Like '%Win 10%'with
And tsysOS.OSname <> 'Win 10'would be more efficient. If you want to anticipate Microsoft adding to the "Win 10" name, maybe
And tsysOS.OSname Not Like 'Win 10%'We're talking milliseconds at most, but if you're wanting to look at this as a general optimization exercise, it can be a good habit to form.
Fast: exact match: OSname = 'Win 10'
Mid: starts with: OSname LIKE 'Win 10%'
Mid: ends with : OSname LIKE '%Win 10'
Slow: contains : OSname LIKE '%Win 10%'
‎08-01-2019 10:18 AM
‎07-31-2019 06:32 PM
‎07-31-2019 12:13 PM
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole < 2
And tsysOS.OSname Not Like '%Win 10%'
And ( (tblADComputers.OU Like '%tours%')
Or (tblADComputers.OU Like '%blois%')
Or (tblADComputers.OU Like '%Orleans%')
Or (tblADComputers.OU Like '%chatellerault%')
Or (tblADComputers.OU Like '%poitiers%')
Or (tblADComputers.OU Like '%nogent%')
Or (tblADComputers.OU Like '%chateaudun%')
Or (tblADComputers.OU Like '%le mans%')
Or (tblADComputers.OU Like '%Chartres%')
)
Where tsysOS.OSname Not Like '%Win 10%' And ((tblADComputers.OU Like '%tours%')
Or (tblADComputers.OU Like '%blois%') Or (tblADComputers.OU Like
'%Orleans%') Or (tblADComputers.OU Like '%chatellerault%') Or
(tblADComputers.OU Like '%poitiers%') Or (tblADComputers.OU Like '%nogent%')
Or (tblADComputers.OU Like '%chateaudun%') Or
(tblADComputers.OU Like '%le mans%') Or
(tblADComputers.OU Like '%Chartres%')) And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2
‎07-31-2019 11:31 AM
‎07-30-2019 10:36 PM
Where
(tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%tours%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%blois%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Orleans%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chatellerault%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%poitiers%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%nogent%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chateaudun%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%le mans%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Chartres%' And tblAssetCustom.State = 1 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole < 2
And tsysOS.OSname Not Like '%Win 10%'
And ( (tblADComputers.OU Like '%tours%')
Or (tblADComputers.OU Like '%blois%')
Or (tblADComputers.OU Like '%Orleans%')
Or (tblADComputers.OU Like '%chatellerault%')
Or (tblADComputers.OU Like '%poitiers%')
Or (tblADComputers.OU Like '%nogent%')
Or (tblADComputers.OU Like '%chateaudun%')
Or (tblADComputers.OU Like '%le mans%')
Or (tblADComputers.OU Like '%Chartres%')
)
OSnamereplacing
-----------
Not scanned
NT 3.51
NT 4
Win 10
Win 2000
Win 2000 S
Win 2003
Win 2003 R2
Win 2008
Win 2008 R2
Win 2012
Win 2012 R2
Win 2016
Win 2019
Win 7
Win 7 RC
Win 8
Win 8.1
Win Home
Win Vista
Win XP
And tsysOS.OSname Not Like '%Win 10%'with
And tsysOS.OSname <> 'Win 10'would be more efficient. If you want to anticipate Microsoft adding to the "Win 10" name, maybe
And tsysOS.OSname Not Like 'Win 10%'We're talking milliseconds at most, but if you're wanting to look at this as a general optimization exercise, it can be a good habit to form.
Fast: exact match: OSname = 'Win 10'
Mid: starts with: OSname LIKE 'Win 10%'
Mid: ends with : OSname LIKE '%Win 10'
Slow: contains : OSname LIKE '%Win 10%'
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now