→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
FelixRu
Engaged Sweeper
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!
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
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

View solution in original post

2 REPLIES 2
FelixRu
Engaged Sweeper
Thank you very much, didnt know I have to state an AND. It works now 🙂
Andy_Sismey
Champion Sweeper III
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