Query multiple fields for reports

[aemberland]

So I'm not a big SQL guy but I'm trying to make my life a little more efficient if possible. I tried reading up a bit on SQL stuff first before coming here.

So basically what I'm doing is adding printer cartridges manually into Lansweeper. Inside there I have 5 custom fields that will be looked at.

• CustomField1: Black: 2
  
• CustomField2: Yellow: 2
  
• CustomField3: Cyan: 4

......and so on.

I tested out that the criteria does work even with words in there since it must be only looking for the numeric value

< 'Black: 1'

will then display when Black is lower than 1. So, cool!

Now my dilemma. I have multiple fields. I want this to display if even ONE of these fields goes beneath the threshold. So if all of the other colors are still at 2 - 4 and one reaches below 1 I want the entire Asset to pop up showing that at least one of these fields hit below 1. Is this possible?

What the asset fields itself looks like:
https://gyazo.com/eb768fcf03358f289faa536f30f026fa

This here is what I was trying to do but this won't work UNLESS they all fall below 1. I want it to work even if 1 falls below:
https://gyazo.com/c88c41039ccba4664e8bcfe2cb172fbc

Thanks for any help with this or if it's even possible!

[RCorbeil]

Definitely show us your code.

From what you've presented thus far, I can offer a few pointers and make some suggestions.

First off, the custom fields are all, by default, text values. As it is, using your example, you've assigned the text string "Black: 2" to CustomField1. That means that when, again, from your example, you do a comparison CustomField1 < 'Black: 1', it's going to compare the full text, not just the number as you appear to intend.

Suggestion: Go into Configure > Asset Pages, scroll down to "Asset Custom fields" and define the display name and data type, e.g. Custom01 -> Display name "Black" -> Info Type Numeric. Repeat for the other toner colours.

If you leave the data type as text, you run into the mess of string comparisons when you want to do numeric, so, for example, "10" < "2" because text comparisons work left-to-right, so it starts by comparing "1" to "2". You can get around it by converting the text to numeric in the query, before doing the comparison, but that's inefficient.

Having said all that, depending on exactly what you're doing this for, the information may already be present in the database. If you're doing this to monitor toner levels of network-connected printers, you could check whether LANSweeper is already reading that information from your printers.

Select Top 1000000
  tblState.Statename,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  'http://' + tblAssets.IPAddress As [hyperlink_IP Address],
  tblAssets.IPNumeric As [IP (sortable)],
  tblAssets.IPAddress As [hyperlink_name_IP Address],
  tblAssets.Mac As [MAC Address],
  tblAssets.Lastseen,
  tblAssetCustom.Location,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Printedpages,
  tblAssetCustom.PrintedColorPages,
  tblAssetCustom.PrintedMonoPages,
  tblAssetCustom.Printerstatus,
  tblCustDevPrinter.TonerNr,
  tblCustDevPrinter.TonerName,
  tblCustDevPrinter.TonerColorName,
  tblCustDevPrinter.TonerColorNr,
  tblCustDevPrinter.TonerMaximum,
  tblCustDevPrinter.TonerRemaining,
  Convert(VarChar(10), Cast( CASE
                               WHEN tblCustDevPrinter.TonerMaximum > 0 AND tblCustDevPrinter.TonerRemaining >= 0
                               THEN tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
                             END AS Decimal(6,2)) ) + '%' AS [Toner %],
  tblCustDevPrinter.LastChanged
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblState On tblState.State = tblAssetCustom.State
  LEFT JOIN tblCustDevPrinter ON tblCustDevPrinter.AssetID = tblAssets.AssetID
Where
  tsysAssetTypes.AssetTypename = 'Printer'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Maintenance%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Drum%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Fuser%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Transfer%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Waste%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Collection%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Clean%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Roller%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Fan Filter%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Kit%'
  AND tblCustDevPrinter.TonerName NOT LIKE '%Belt%'
Order By
  tblAssetCustom.Manufacturer,
  tblAssets.AssetName,
  tblCustDevPrinter.TonerNr

You may need to expand the filter list if your inventory has more than just toner in the output.

[rader]

Would you post your code here so we could see?