Help with new report

[QWERTY2022]

I am trying to create a new report. I keep having Invalid Select Statement. Unexpected token "." This is my first report I have ever created. I want to have all assets show Asset Name, Asset Type, State( Active or Non-Active), Asset Model, Asset Serial number, Last User, OS Name, OS Build, Os Version, Antivirus, IP Address, Ip Location, Enabled in AD (Yes or No), Domain, First Seen, Last Seen, Lasted Scanned, Purchased Date, Warranty EOL,(Expired Yes or no) SCCM Last scanned


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tblstate.State,
tblstate.Statename
tsysOS.Image As icon
tsysOS.OSname As [OS name],
tsysOS.OSCodeNumeric As [OS],
tblAssets.OScode As [OS Build],
Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version
Else tblAssetCustom.Model
End As Model,
tblAssetCustom.Serialnumber,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Location As CustomLocation,
tsysIPLocations.IPLocation,
tblADComputers.Location As ADlocation,
tblAssets.Domain,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAntivirus.DisplayName As AntiVirus
tblAntivirus.ProductState As AV State
tblAntivirus.onaccessScanningEnabled As AV Enabled
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblSccmAsset.LastScanDate As SCCM Last Scan,
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tsysOS
On tsysOS.OScode = tblAssets.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblstate On tblstate.State = tblAssetCustom.State
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where tblAssets.AssetID Not In (Select Distinct tblAntivirus.AssetID
From tblAntivirus Where (Case
When tblAntivirus.onAccessScanningEnabled <>
0 Then 'Antivirus Enabled or Unknown'
End) = 'Antivirus Enabled or Unknown') And tblState.Statename = 'Active' And
tblDomainroles.Domainrolename In ('Stand-alone Workstation',
'Member Workstation')
From tblSccmAsset
Left Join tblAssets On tblSccmAsset.AssetId = tblAssets.AssetID

Where (tblSccmAsset.AssetId Is Null) Or
(tblAssets.ScannedBy = 2)

Order By tblAssets.AssetName

[KevinA-REJIS]

I had to move some things around, but I was able to get the code below to run - however there were no hits, so I may still have something missing, or none of our assets match the criteria:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tblstate.State,
  tblstate.Statename,
  tsysOS.Image As icon,
  tsysOS.OSname As [OS name],
  tsysOS.OSCodeNumeric As OS,
  tblAssets.OScode As [OS Build],
  Case
    When tblAssetCustom.Manufacturer Like '%lenovo%' Then
      tblComputerSystemProduct.Version
    Else tblAssetCustom.Model
  End As Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssetCustom.Location As CustomLocation,
  tsysIPLocations.IPLocation,
  tblADComputers.Location As ADlocation,
  tblAssets.Domain,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAntivirus.DisplayName As AntiVirus,
  tblAntivirus.ProductState As [AV State],
  tblAntivirus.onAccessScanningEnabled As [AV Enabled],
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblSccmAsset.LastScanDate As [SCCM Last Scan]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
  Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
      tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblstate On tblstate.State = tblAssetCustom.State
  Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
  Inner Join tblComputerSystem On tblAssets.AssetID = tblComputerSystem.AssetID
  Inner Join tblDomainroles On tblComputerSystem.Domainrole =
      tblDomainroles.Domainrole
  Inner Join tblComputerSystemProduct On tblAssets.AssetID =
      tblComputerSystemProduct.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Where tblAssets.AssetID Not In (Select Distinct tblAntivirus.AssetID
    From tblAntivirus Where (Case
        When tblAntivirus.onAccessScanningEnabled <> 0 Then
          'Antivirus Enabled or Unknown'
      End) = 'Antivirus Enabled or Unknown') And tblstate.Statename = 'Active'
  And ((tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
    (tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)) And
  ((tblSccmAsset.AssetId Is Null) Or (tblAssets.ScannedBy = 2)) And
  tblDomainroles.Domainrolename In ('Stand-alone Workstation',
  'Member Workstation')
Order By tblAssets.AssetName