Notification

Icon
Error

Customized Printer Toner Report - Assets with WAY higher "percent left" numbers

Posted: Friday, August 7, 2020 9:37:50 PM(UTC)
Cripple.Zero

Cripple.Zero

Member Original PosterPosts: 33
0
Like
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]
Handles attachments
LS-percentage.jpg (364kb) downloaded 8 time(s).
RC62N
#1RC62N Member Posts: 470  
posted: 8/7/2020 9:54:22 PM(UTC)
Unfortunately, not all printers report their consumables as percentages. Here's how I've handled it on a few of my reports:
Code:

Convert( VarChar(10),
         Cast( Case
                 When tblCustDevPrinter.TonerMaximum > 0
                 Then tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
               End
         As Decimal(6,2))) + '%' As Pct
Cripple.Zero
#2Cripple.Zero Member Original PosterPosts: 33  
posted: 8/10/2020 2:10:27 PM(UTC)
Apologies for not following the logic, but where would I place this code? Or is this replacing a string?
RC62N
#3RC62N Member Posts: 470  
posted: 8/10/2020 2:54:18 PM(UTC)
Substitute for your RemainingPercentage.
Code:
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.
Code:
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,
...
Cripple.Zero
#4Cripple.Zero Member Original PosterPosts: 33  
posted: 8/10/2020 4:11:24 PM(UTC)
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.
RC62N
#5RC62N Member Posts: 470  
posted: 8/10/2020 4:36:21 PM(UTC)
Code:
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')
RC62N
#6RC62N Member Posts: 470  
posted: 8/10/2020 5:08:24 PM(UTC)
Another view:
Code:
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.
Cripple.Zero
#7Cripple.Zero Member Original PosterPosts: 33  
posted: 8/10/2020 9:08:00 PM(UTC)
HOLY FRIJOLES BATMAN!!!

That just cleaned up that report A TON! WOW

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

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

MAGNAFIQUE!!! Dancing Dancing Dancing

Active Discussions

Lansweeper zerologin posted report
by  Antikas   Go to last post Go to first unread
Last post: Today at 9:42:54 AM(UTC)
Lansweeper Report doesn't show empty results for a field
by  AlexMZetec  
Go to last post Go to first unread
Last post: Yesterday at 3:43:08 PM(UTC)
Lansweeper Renamed Pcs / Laptops report
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 3:36:35 PM(UTC)
Lansweeper Servers without AV Report
by  Elwood472  
Go to last post Go to first unread
Last post: 9/27/2020 2:50:10 AM(UTC)
Lansweeper Adding Group by and Sum to Existing Report
by  RC62N  
Go to last post Go to first unread
Last post: 9/25/2020 3:43:49 PM(UTC)
Lansweeper Custom Fields on Report for Helpdesk Tickets
by  plangham_eurotech   Go to last post Go to first unread
Last post: 9/24/2020 2:43:41 PM(UTC)
Lansweeper September Patch Tuesday
by  Gilles B.  
Go to last post Go to first unread
Last post: 9/24/2020 7:47:49 AM(UTC)