Customized Printer Toner Report

mhammond · ‎08-07-2020

I am working on a custom report, modified from the built-in "low toner" report. I am working on trying generate the report based on 30% toner remaining or less. However, one part really bothers me .... in the "RemainingPercentage" column, not only am I seeing assets that have above 30%, but some that are reporting THOUSANDS of percent left. What is calculating the percent and WHY is it so high? I have one asset reporting over 1,000,000% remaining! What have I done wrong???

Below is the code: (screenshot attached)

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Printer Name],
tblAssetCustom.Custom11 As [MT Asset Tag],
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [Facility Location],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(tblCustDevPrinter.TonerRemaining As nvarchar) +
'%' As RemainingPercentage,
tblCustDevPrinter.Tonername As [Toner Type],
tblCustDevPrinter.TonerColorName As [Toner Color]
From tblCustDevPrinter
Inner Join tblAssets On tblCustDevPrinter.AssetID = tblAssets.AssetID
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 Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.IPAddress Like '10.1.2.%' And tsysAssetTypes.AssetTypename =
'Printer' And tblCustDevPrinter.TonerMaximum <> 0 And
tblCustDevPrinter.TonerMaximum > 0 And tblCustDevPrinter.TonerRemaining <> 0
And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) <= 30 And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) >= 0 And tblState.Statename = 'Active') Or
(tblAssets.IPAddress Like '10.2.2.%') Or
(tblAssets.IPAddress Like '10.6.2.%')
Order By [Facility Location],
[Printer Name]

mhammond · ‎08-10-2020

HOLY FRIJOLES BATMAN!!!

That just cleaned up that report A TON! WOW

That's really impressive - here's to hard work! :cheers:

It's so much cleaner and easier to read .. and it whittles it down to the NEEDED information.

MAGNAFIQUE!!!

RCorbeil · ‎08-10-2020

Another view:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName As [Printer Name],
  tblAssetCustom.Custom11 As [MT Asset Tag],
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation As [Facility Location],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Cast(Floor(c.TonerRemaining / (Case
                                   When c.TonerMaximum = 0
                                   Then 1
                                   Else c.TonerMaximum
                                 End) * 100) As nvarchar) + '%' As Cyan,
  Cast(Floor(m.TonerRemaining / (Case
                                   When m.TonerMaximum = 0
                                   Then 1
                                   Else m.TonerMaximum
                                 End) * 100) As nvarchar) + '%' As Magenta,
  Cast(Floor(y.TonerRemaining / (Case
                                   When y.TonerMaximum = 0
                                   Then 1
                                   Else y.TonerMaximum
                                 End) * 100) As nvarchar) + '%' As Yellow,
  Cast(Floor(k.TonerRemaining / (Case
                                   When k.TonerMaximum = 0
                                   Then 1
                                   Else k.TonerMaximum
                                 End) * 100) As nvarchar) + '%' As Black
From
  tblAssets
  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 Join tsysOS On tsysOS.OScode = tblAssets.OScode

  LEFT JOIN tblCustDevPrinter AS c ON c.AssetID = tblAssets.AssetID
                                         AND (c.TonerColorName LIKE '%cyan%'
                                              OR c.Tonername LIKE '%cyan%')
                                         AND c.Tonername NOT LIKE '%fuser%'
                                         AND c.Tonername NOT LIKE '%drum%'
                                         AND c.Tonername NOT LIKE '%image%'
                                         AND c.Tonername NOT LIKE '%maintenance%'
                                         AND c.Tonername NOT LIKE '%transfer%'
                                         AND c.Tonername NOT LIKE '%waste%'
                                         AND c.Tonername NOT LIKE '%feed roller%'
                                         AND c.Tonername NOT LIKE '%filter%'
                                         And c.TonerMaximum <> 0
                                         And c.TonerMaximum > 0
                                         And c.TonerRemaining <> 0
                                         AND Floor(c.TonerRemaining / (Case
                                                    When c.TonerMaximum = 0
                                                    Then 1
                                                    Else c.TonerMaximum
                                                  End) * 100) <= 30
                                         AND Floor(c.TonerRemaining / (Case
                                                    When c.TonerMaximum = 0
                                                    Then 1
                                                    Else c.TonerMaximum
                                                  End) * 100) >= 0
  LEFT JOIN tblCustDevPrinter AS M ON m.AssetID = tblAssets.AssetID
                                         AND (m.TonerColorName LIKE '%magenta%'
                                              OR m.Tonername LIKE '%magenta%')
                                         AND m.Tonername NOT LIKE '%fuser%'
                                         AND m.Tonername NOT LIKE '%drum%'
                                         AND m.Tonername NOT LIKE '%image%'
                                         AND m.Tonername NOT LIKE '%maintenance%'
                                         AND m.Tonername NOT LIKE '%transfer%'
                                         AND m.Tonername NOT LIKE '%waste%'
                                         AND m.Tonername NOT LIKE '%feed roller%'
                                         AND m.Tonername NOT LIKE '%filter%'
                                         And m.TonerMaximum <> 0
                                         And m.TonerMaximum > 0
                                         And m.TonerRemaining <> 0
                                         AND Floor(m.TonerRemaining / (Case
                                                    When m.TonerMaximum = 0
                                                    Then 1
                                                    Else m.TonerMaximum
                                                  End) * 100) <= 30
                                         AND Floor(m.TonerRemaining / (Case
                                                    When m.TonerMaximum = 0
                                                    Then 1
                                                    Else m.TonerMaximum
                                                  End) * 100) >= 0
  LEFT JOIN tblCustDevPrinter AS Y ON y.AssetID = tblAssets.AssetID
                                         AND (y.TonerColorName LIKE '%yellow%'
                                              OR y.Tonername LIKE '%yellow%')
                                         AND y.Tonername NOT LIKE '%fuser%'
                                         AND y.Tonername NOT LIKE '%drum%'
                                         AND y.Tonername NOT LIKE '%image%'
                                         AND y.Tonername NOT LIKE '%maintenance%'
                                         AND y.Tonername NOT LIKE '%transfer%'
                                         AND y.Tonername NOT LIKE '%waste%'
                                         AND y.Tonername NOT LIKE '%feed roller%'
                                         AND y.Tonername NOT LIKE '%filter%'
                                         And y.TonerMaximum <> 0
                                         And y.TonerMaximum > 0
                                         And y.TonerRemaining <> 0
                                         AND Floor(y.TonerRemaining / (Case
                                                    When y.TonerMaximum = 0
                                                    Then 1
                                                    Else y.TonerMaximum
                                                  End) * 100) <= 30
                                         AND Floor(y.TonerRemaining / (Case
                                                    When y.TonerMaximum = 0
                                                    Then 1
                                                    Else y.TonerMaximum
                                                  End) * 100) >= 0
  LEFT JOIN tblCustDevPrinter AS K ON k.AssetID = tblAssets.AssetID
                                         AND (k.TonerColorName LIKE '%black%'
                                              OR k.Tonername LIKE '%black%'
                                              OR (k.TonerColorName = 'Toner' AND k.Tonername = 'Toner') )
                                         AND k.Tonername NOT LIKE '%fuser%'
                                         AND k.Tonername NOT LIKE '%drum%'
                                         AND k.Tonername NOT LIKE '%image%'
                                         AND k.Tonername NOT LIKE '%maintenance%'
                                         AND k.Tonername NOT LIKE '%transfer%'
                                         AND k.Tonername NOT LIKE '%waste%'
                                         AND k.Tonername NOT LIKE '%feed roller%'
                                         AND k.Tonername NOT LIKE '%filter%'
                                         And k.TonerMaximum <> 0
                                         And k.TonerMaximum > 0
                                         And k.TonerRemaining <> 0
                                         AND Floor(k.TonerRemaining / (Case
                                                    When k.TonerMaximum = 0
                                                    Then 1
                                                    Else k.TonerMaximum
                                                  End) * 100) <= 30
                                         AND Floor(k.TonerRemaining / (Case
                                                    When k.TonerMaximum = 0
                                                    Then 1
                                                    Else k.TonerMaximum
                                                  End) * 100) >= 0
Where
  ( tblAssets.IPAddress Like '10.1.2.%'
    And tsysAssetTypes.AssetTypename = 'Printer'
    -- only records with at least one low toner/ink
    AND (   c.AssetID IS NOT NULL
         OR m.AssetID IS NOT NULL
         OR y.AssetID IS NOT NULL
         OR k.AssetID IS NOT NULL)
    And tblState.Statename = 'Active')
  Or (tblAssets.IPAddress Like '10.2.2.%')
  Or (tblAssets.IPAddress Like '10.6.2.%')
Order By
  [Facility Location],
  [Printer Name]

I've probably gone a little overboard on the non-toner/ink filters, but I show instances in my inventory of TonerName LIKE %fuser% and TonerColorName = 'black', so I figure better to over-filter than under.

RCorbeil · ‎08-10-2020

Where
  ( tblAssets.IPAddress Like '10.1.2.%'
    And tsysAssetTypes.AssetTypename = 'Printer'
    And tblCustDevPrinter.TonerMaximum <> 0
    And tblCustDevPrinter.TonerMaximum > 0
    And tblCustDevPrinter.TonerRemaining <> 0
    And Floor(tblCustDevPrinter.TonerRemaining / (Case
                                                    When tblCustDevPrinter.TonerMaximum = 0
                                                    Then 1
                                                    Else tblCustDevPrinter.TonerMaximum
                                                  End) * 100) <= 30
    And Floor(tblCustDevPrinter.TonerRemaining / (Case
                                                    When tblCustDevPrinter.TonerMaximum = 0
                                                    Then 1
                                                    Else tblCustDevPrinter.TonerMaximum
                                                  End) * 100) >= 0
  -- include explicitly-named toner/ink
  AND ( tblCustDevPrinter.TonerColorName LIKE '%cyan%'
        OR tblCustDevPrinter.TonerColorName LIKE '%magenta%'
        OR tblCustDevPrinter.TonerColorName LIKE '%yellow%'
        OR tblCustDevPrinter.TonerColorName LIKE '%black%'
        OR tblCustDevPrinter.Tonername LIKE '%cyan%'
        OR tblCustDevPrinter.Tonername LIKE '%magenta%'
        OR tblCustDevPrinter.Tonername LIKE '%yellow%'
        OR tblCustDevPrinter.Tonername LIKE '%black%'
      )
  -- exclude things that aren't toner/ink
  AND tblCustDevPrinter.Tonername NOT LIKE '%fuser%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%drum%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%image%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%maintenance%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%transfer%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%waste%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%feed roller%'
  AND tblCustDevPrinter.Tonername NOT LIKE '%filter%'
  And tblState.Statename = 'Active')

mhammond · ‎08-10-2020

THANK YOU!!!!

That definitely eliminated the "THOUSANDS" and "MILLIONS" percent entries in the report.

Now, all I have to do is figure a way to only show "black", "cyan", "magenta", and "yellow" as the toner name that is low (or exclude toner types waste toner or fuser or maintenance kit); because now, it's hitting several MFPs, but it is including those extra parts.

I'm not too worried about getting TOO granular, as in MFPs: say, a yellow is at or below 30% but the rest of the colors are well above, so they show on the report as above 30% remaining; it makes sense why those entries duplicate - since you can't really separate them from the machine.

RCorbeil · ‎08-10-2020

Substitute for your RemainingPercentage.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName As [Printer Name],
  tblAssetCustom.Custom11 As [MT Asset Tag],
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation As [Facility Location],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Convert( VarChar(10),
         Cast( Case
                 When tblCustDevPrinter.TonerMaximum > 0
                 Then tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
               End
         As Decimal(6,2))) + '%' As RemainingPercentage,
...

The funny thing is, you've handled it in your WHERE clause. You could just as easily copy your code from there to use as the RemainingPercentage value.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName As [Printer Name],
  tblAssetCustom.Custom11 As [MT Asset Tag],
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation As [Facility Location],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Cast(Floor(tblCustDevPrinter.TonerRemaining / (Case
                                                   When tblCustDevPrinter.TonerMaximum = 0
                                                   Then 1
                                                   Else tblCustDevPrinter.TonerMaximum
                                                 End) * 100) As nvarchar) + '%' As RemainingPercentage,
...

mhammond · ‎08-10-2020

Apologies for not following the logic, but where would I place this code? Or is this replacing a string?

RCorbeil · ‎08-07-2020

Unfortunately, not all printers report their consumables as percentages. Here's how I've handled it on a few of my reports:


Convert( VarChar(10),
         Cast( Case
                 When tblCustDevPrinter.TonerMaximum > 0
                 Then tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
               End
         As Decimal(6,2))) + '%' As Pct

Customized Printer Toner Report

New to Lansweeper?

Try Lansweeper For Free