Custom Report for Outdated BIOS based on Model

[JTZRFB]

Issues creating report for outdated BIOS version based on model and Bios version I set as the approved one.

I just have one computer model listed as a test.
Computer Model: Latitude E5540
Approved Current BIOS Version: A21
If BIOS version scanned is lower than the approved version flag as not patched.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.Image As icon,
  tblBIOS.Caption,
  tblBIOS.Manufacturer,
  tblAssetCustom.Model,
  tblBIOS.SerialNumberl,
  tblBIOS.Lastchanged,
  tblBIOS.Version,
  tblBIOS.SMBIOSBIOSVersion As BIOSVersion,
  CASE   WHEN tblAssetCustom.Model = 'Latitude E5540' And tblBIOS.SMBIOSBIOSVersion < 'A21' Then 'Needs Patch'  
  ELSE 'Patched' as BiosPatched
From tblAssets,
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[JTZRFB]

I had some typos, but here is what I came up with.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Domain,
  tsysOS.Image As icon,
  tblBIOS.SerialNumber As Serial,
  tblBIOS.SMBIOSBIOSVersion As [Current Bios Ver],
  tblBIOS.Lastchanged As [Bios Last Changed],
  tblBIOS.ReleaseDate As [BIOS Release Date],
  Case
    When tblAssetCustom.Model = 'Inspiron 3650' And tblBIOS.SMBIOSBIOSVersion <
    '9.9.9.9' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Latitude E5540' And tblBIOS.SMBIOSBIOSVersion <
    'A21' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Latitude E6540' And tblBIOS.SMBIOSBIOSVersion <
    'A24' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Optiplex 390' And tblBIOS.SMBIOSBIOSVersion <
    'A999' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 3010' And tblBIOS.SMBIOSBIOSVersion <
    'A999' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 3020' And tblBIOS.SMBIOSBIOSVersion <
    'A17' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 3020M' And tblBIOS.SMBIOSBIOSVersion <
    'A12' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 3040' And tblBIOS.SMBIOSBIOSVersion <
    '1.6.1' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 3050' And tblBIOS.SMBIOSBIOSVersion <
    '1.7.9' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 7010' And tblBIOS.SMBIOSBIOSVersion <
    'A999' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 7020' And tblBIOS.SMBIOSBIOSVersion <
    'A15' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 7040' And tblBIOS.SMBIOSBIOSVersion <
    '1.8.1' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 7440 AIO' And
    tblBIOS.SMBIOSBIOSVersion < '1.8.6' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 9010' And tblBIOS.SMBIOSBIOSVersion <
    'A999' Then 'Needs Patch'
    When tblAssetCustom.Model = 'OptiPlex 9020' And tblBIOS.SMBIOSBIOSVersion <
    'A22' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Precision M2800' And
    tblBIOS.SMBIOSBIOSVersion < 'A14' Then 'Needs Patch'
    When tblAssetCustom.Model = 'XPS 13 9350' And tblBIOS.SMBIOSBIOSVersion <
    '1.6.1' Then 'Needs Patch'
    When tblAssetCustom.Model = 'XPS 13 9360' And tblBIOS.SMBIOSBIOSVersion <
    '2.5.1' Then 'Needs Patch'
    When tblAssetCustom.Model = 'XPS 15 9560' And tblBIOS.SMBIOSBIOSVersion <
    '1.7.1' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad E570' And tblBIOS.SMBIOSBIOSVersion <
    'ZZZZZZZZ (99.99 )' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad T530' And tblBIOS.SMBIOSBIOSVersion <
    'ZZZZZZZZ (99.99 )' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad T550' And tblBIOS.SMBIOSBIOSVersion <
    'N11ET44WT5 (1.20 )' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad T560' And tblBIOS.SMBIOSBIOSVersion <
    'N1KET36W (1.23 )' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad T570' And tblBIOS.SMBIOSBIOSVersion <
    'ZZZZZZZZ (99.99 )' Then 'Needs Patch'
    When tblAssetCustom.Model = 'ThinkPad X1 Carbon 4th' And
    tblBIOS.SMBIOSBIOSVersion < 'ZZZZZZZZ (99.99 )' Then 'Needs Patch'
    Else 'Patched' End As [BIOS Patched]
From tblAssets
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Manufacturer Not Like 'VMware, Inc.' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName

[RCorbeil]

Issues creating report...

What issues? It helps us offer suggestions if we have an idea of what you're trying to do and what you think is wrong.

First thing I see is that you didn't END your CASE. Without that, the query doesn't run at all.

One possible problem I see is that, because you're reporting on all active assets but only checking the state of one model (or however many specific models you end up checking), everything outside of that list of models is being reported as "patched" even though you haven't checked them. If it were me, I'd constrain things, limiting the output to only the models being checked, e.g.

Where
  tblAssetCustom.State = 1
  AND tblAssetCustom.Model In ('Latitude E5540', 'Model 1', 'Model 2')

Alternatively, you might consider checking for both patched and unpatched values and leave the ELSE as a fall-through for anything you haven't checked, e.g.

  Case
    When tblAssetCustom.Model = 'Latitude E5540' And tblBIOS.SMBIOSBIOSVersion <  'A21' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Latitude E5540' And tblBIOS.SMBIOSBIOSVersion >= 'A21' Then 'Patched'

    When tblAssetCustom.Model = 'Model 1' And tblBIOS.SMBIOSBIOSVersion <  'A21' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Model 1' And tblBIOS.SMBIOSBIOSVersion >= 'A21' Then 'Patched'

    When tblAssetCustom.Model = 'Model 2' And tblBIOS.SMBIOSBIOSVersion <  'A21' Then 'Needs Patch'
    When tblAssetCustom.Model = 'Model 2' And tblBIOS.SMBIOSBIOSVersion >= 'A21' Then 'Patched'

    Else 'Not checked'
  End As BiosPatched

You should also bear in mind the the BIOS version is stored as a string, so you'll be doing ASCII-betical comparisons. If all you're dealing with is "A00"-format values, it's not a big deal, but if a machine has BIOS version 1.9.3 and the latest is 1.10.7, "1.9" is greater than "1.1" so you'll need to be craftier about handling the possibility. (I only bring it up because I see from my inventory that Dell has moved from the "A00" format to "x.y.z" on newer machines.)