Report with only specific Assets

[FelixRu]

Hello, I tried to edit the already persisting Lansweeper report for UPtime of assets to show me only assets starting with the name ipc-schl. I already went through the ones that other community members created but I always get for some reason an error see the query below:

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Case
When tsysOS.osname Is Not Null Then tsysOS.osname
When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
When tblMacOSInfo.SystemVersion Is Not Null Then tblMacOSInfo.SystemVersion
When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion)
Else ''
End As OS,
tblAssets.SP,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot
From tblAssets
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Where tblState.Statename = 'Active' And tblAssets.Uptime Is Not Null
tblAssets.AssetName Like 'ipc-schl%' <- Invalid Statement unexpected token
Order By tblAssets.Uptime Desc,
tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName

My SQL skills are very limited, so I'm happy for any help!

[FelixRu]

Thank you very much, didnt know I have to state an AND. It works now 🙂

[Andy_Sismey · Accepted solution]

Hi,

Looks like your missing an "AND"

Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Where tblState.Statename = 'Active' And tblAssets.Uptime Is Not Null AND
tblAssets.AssetName Like 'ipc-schl%' 
Order By tblAssets.Uptime Desc,
tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName