→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
Hi Team,

Looking for a way to filter based on IP Range. And computers that are missing said mention printer for that site / IP range.

Trying to have it filter for single computers that are missing the printer where as I am getting multiple results.



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblPrinters.Caption As Printer,
tblPrinters.Portname As PrinterPort,
tblPrinters.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.IPAddress Like '10.40.12.%' And tblPrinters.Caption Not Like
'PRINTERNAME'
Order By tblAssets.Domain,
tblAssets.AssetName,
Printer
1 REPLY 1
Andy_Sismey
Champion Sweeper III
Probably not the best method but one way I use is to create a Yes / No field to identify all the assets with the Printer and then just list all the 'No' assets something like this :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When Printer.AssetID Is Not Null Then 'Yes'
Else 'No'
End As 'Printer installed',
tblAssets.Domain,
tblAssets.Username,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join (Select tblPrinters.Lastchanged,
tblPrinters.Portname,
tblPrinters.Caption,
tblPrinters.AssetID
From lansweeperdb.dbo.tblPrinters
Where tblPrinters.Caption Like '%PRINTERNAME%') Printer On Printer.AssetID =
tblAssets.AssetID
Where Case
When Printer.AssetID Is Not Null Then 'Yes'
Else 'No'
End = 'No' And tblAssets.IPAddress Like '10.40.12.%' And tblAssetCustom.State = 1
And tsysAssetTypes.AssetTypename In ('Windows')
Order By tblAssets.Domain,
tblAssets.AssetName