Case - Trabalhando no Lansweeper

SQL Server

SQL

23/11/2021

Prezados.
Trabalhando no Lansweeper, fiz um script para trazer toda impressora que esteja com cilindro/toner <=10%

porém, tem uma impressora, que quando está com >=6%, ainda imprime muita coisa.

Então, gostaria de traze-la apenas quando ela chegar a <=5%

Como posso trabalhar essa consulta ???

O script utilizado.

Select *
From (Select tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName As Hostname,
tblAssetCustom.Model As Modelo,
Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) As Restando,
Case
When (tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum
* 100) <= 5 Then ''''red''''
End As foregroundcolor,
tblCustDevPrinter.Tonername As Insumos,
tblAssetCustom.Location As Localizacao,
tblAssets.IPAddress,
tblAssets.Lastseen As [ultima visualizacao]
From tblCustDevPrinter
Inner Join tblAssets On tblCustDevPrinter.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On
tblAssets.Assettype = tsysAssetTypes.AssetType
Where (tblAssets.IPAddress Between ''''10.111.9.1'''' And ''''10.111.9.255'''' ) And
tblAssetCustom.State = 1 And tblCustDevPrinter.TonerRemaining >= 0 And
tblCustDevPrinter.TonerMaximum > 0) As [Trocar Toner]
Where [Trocar Toner].Restando >= 0 And [Trocar Toner].Restando <= 10
Order By [Trocar Toner].Restando
Pedro Costa

Pedro Costa

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

23/11/2021

tente algo assim:
Select
	*,
	Case
		When Restando <= (case when IPAddress = 'IP DA IMPRESSORA BALA!' then 2 else 5 end)
		Then 'red'
		Else 'green'
	End As foregroundcolor
From (
	Select
		tsysAssetTypes.AssetTypeIcon10 As icon,
		tblAssets.AssetName As Hostname,
		tblAssetCustom.Model As Modelo,
		Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100) As Restando,
		tblCustDevPrinter.Tonername As Insumos,
		tblAssetCustom.Location As Localizacao,
		tblAssets.IPAddress,
		tblAssets.Lastseen As [ultima visualizacao]
	From
		tblCustDevPrinter
	Inner Join
		tblAssets On
		tblCustDevPrinter.AssetID = tblAssets.AssetID
	Inner Join
		tblAssetCustom On
		tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join
		tsysAssetTypes On
		tblAssets.Assettype = tsysAssetTypes.AssetType
	Where
		(tblAssets.IPAddress Between '10.111.9.1' And '10.111.9.255')
		And tblAssetCustom.State = 1
		And tblCustDevPrinter.TonerRemaining >= 0
		And tblCustDevPrinter.TonerMaximum > 0
) As [Trocar Toner]
Where
	Restando >= 0
	And Restando <= (case when IPAddress = 'IP DA IMPRESSORA BALA!' then 5 else 10 end)
Order By
	Restando
GOSTEI 0
Pedro Costa

Pedro Costa

23/11/2021

Obrigado. Me ajudou...
trabalhei assim :

As [Trocar Toner]
Where [Trocar Toner].Restando >= 0 And [Trocar Toner].Restando <= (Case
When [Trocar Toner].Modelo = 'MPS5501b' Then 5
When [Trocar Toner].Modelo = 'MPS5502b' Then 5
Else 10
End)
Order By [Trocar Toner].Restando
GOSTEI 0
POSTAR