cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
QWERTY2022
Engaged Sweeper
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
1 REPLY 1
KevinA-REJIS
Champion Sweeper III
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