→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
skepper
Engaged Sweeper
Hi All
i can`t run report
"Device: Printer almost out of toner"
can someone give me the query ?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please try the SQL query below instead.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssetCustom.Model As [Device model],
tblAssets.AssetID,
Floor(tblCustDevPrinter.TonerRemaining / (Case tblCustDevPrinter.TonerMaximum
When 0 Then 1 Else tblCustDevPrinter.TonerMaximum
End) * 100) As [Remaining %],
tblCustDevPrinter.TonerMaximum,
tblCustDevPrinter.Tonername,
tblCustDevPrinter.Lastchanged
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 (Floor(tblCustDevPrinter.TonerRemaining /
(Case tblCustDevPrinter.TonerMaximum When 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) <= 10 And Floor(tblCustDevPrinter.TonerRemaining / (Case
tblCustDevPrinter.TonerMaximum When 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) >= 0) And tblCustDevPrinter.TonerMaximum > 0 And
tblAssetCustom.State = 1
Order By [Remaining %]

View solution in original post

3 REPLIES 3
skepper
Engaged Sweeper


10x !!!
Hemoco
Lansweeper Alumni
Please try the SQL query below instead.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssetCustom.Model As [Device model],
tblAssets.AssetID,
Floor(tblCustDevPrinter.TonerRemaining / (Case tblCustDevPrinter.TonerMaximum
When 0 Then 1 Else tblCustDevPrinter.TonerMaximum
End) * 100) As [Remaining %],
tblCustDevPrinter.TonerMaximum,
tblCustDevPrinter.Tonername,
tblCustDevPrinter.Lastchanged
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 (Floor(tblCustDevPrinter.TonerRemaining /
(Case tblCustDevPrinter.TonerMaximum When 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) <= 10 And Floor(tblCustDevPrinter.TonerRemaining / (Case
tblCustDevPrinter.TonerMaximum When 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) >= 0) And tblCustDevPrinter.TonerMaximum > 0 And
tblAssetCustom.State = 1
Order By [Remaining %]
skepper
Engaged Sweeper
the error :

error while getting report
Dividing by zero is not allowed. [ Name of function (if known) = ]

the query is:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssetCustom.Model As [Device model],
tblAssets.AssetID,
Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) As [Remaining %],
tblCustDevPrinter.TonerMaximum,
tblCustDevPrinter.Tonername,
tblCustDevPrinter.Lastchanged
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 (Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) <= 10 And Floor(tblCustDevPrinter.TonerRemaining /
tblCustDevPrinter.TonerMaximum * 100) >= 0) And tblCustDevPrinter.TonerMaximum
> 0 And tblAssetCustom.State = 1
Order By Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum
* 100)