cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rader
Champion Sweeper III
My dashboard with a chart quick glance at toner levels, and a colorized report with more toner details set to highlight items with 10% remaining. If LS has info on the printer it can show the reorder # of the toner as well.

===================
Chart: Toner Levels
===================


Select Top 1000000 tblAssetCustom.Model,
tblCustDevPrinter.TonerRemaining As [Copies Left]
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblCustDevPrinter.TonerRemaining >= 0 And tblCustDevPrinter.TonerMaximum >
0 And tblAssetCustom.State = 1
Group By tblAssetCustom.Model,
tblCustDevPrinter.TonerRemaining,
tblCustDevPrinter.TonerColorName,
tblCustDevPrinter.Tonername,
tblCustDevPrinter.TonerMaximum
Order By tblAssetCustom.Model


====================
Report: Toner Levels
====================


Select Top 1000000 tblAssetCustom.Model,
tblCustDevPrinter.TonerRemaining As [Copies Left],
tblCustDevPrinter.TonerMaximum As [Max. Copies],
Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) As [Remaining %],
tblCustDevPrinter.Tonername As [Reorder #],
tblCustDevPrinter.TonerColorName As Color,
tblAssets.IPAddress,
Case
When (tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) <= 10 Then 'red'
End As foregroundcolor,
Case
When (tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
100) <= 10 Then 'yellow'
End As backgroundcolor
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblCustDevPrinter.TonerRemaining >= 0 And tblCustDevPrinter.TonerMaximum >
0 And tblAssetCustom.State = 1
Group By tblAssetCustom.Model,
tblCustDevPrinter.TonerRemaining,
tblCustDevPrinter.TonerMaximum,
tblCustDevPrinter.Tonername,
6 REPLIES 6
rader
Champion Sweeper III

Use the first bit of code in the Chart: Toner Level area, making sure to label the report as Chart: first, then when adding the widget to the dashboard, pick chart and add the appropriate chart.

Easy peasy.

I got the chart to work but doesnt show the bars just numbers on the chart.

rader
Champion Sweeper III

Here's a screenshot of my chart parameters.

2023-03-29 12_40_01-Toner Levels - Lansweeper — Mozilla Firefox.png

rzubulake
Engaged Sweeper

How do you make the chart?

OBradleyinUSA
Engaged Sweeper

The report toner levels give me an error.

rader
Champion Sweeper III

Not sure what happened but the report seems to be missing the last 8 lines. Here's the correct report...

Select Top 1000000 tblAssetCustom.Model,
  tblCustDevPrinter.TonerRemaining As [Copies Left],
  tblCustDevPrinter.TonerMaximum As [Max. Copies],
  Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
  100) As [Remaining %],
  tblCustDevPrinter.Tonername As [Reorder #],
  tblCustDevPrinter.TonerColorName As Color,
  tblAssets.IPAddress,
  Case
    When (tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
      100) <= 10 Then 'red'
  End As foregroundcolor,
  Case
    When (tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
      100) <= 10 Then 'yellow'
  End As backgroundcolor,
  tblAssetCustom.SnmpOID,
  tblAssetCustom.Printedpages,
  tblAssetCustom.Printerstatus
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
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
      And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblCustDevPrinter.TonerRemaining >= 0 And tblCustDevPrinter.TonerMaximum >
  0 And tblAssetCustom.State = 1
Group By tblAssetCustom.Model,
  tblCustDevPrinter.TonerRemaining,
  tblCustDevPrinter.TonerMaximum,
  tblCustDevPrinter.Tonername,
  tblCustDevPrinter.TonerColorName,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.SnmpOID,
  tblAssetCustom.Printedpages,
  tblAssetCustom.Printerstatus
Order By tblAssetCustom.Model,
  [Copies Left]