Reporting Group

SELECT
   [vri1_Computer].[Guid],
   [vri1_Computer].[Name]
FROM
   [vRM_Computer_Item] AS [vri1_Computer]
      LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca2_HW Logical Device]
         ON ([vri1_Computer].[Guid] = [dca2_HW Logical Device].[_ResourceGuid])
WHERE
   (
      (
         ([dca2_HW Logical Device].[Model] LIKE N'Latitude 5480')
      )
   )

SELECT

C.NAME AS COMPUTER
,C.[OS Name]
,C.[System Type]
,C.[User] [Lat Logon User]
,c.[MAC Address]
,c.[IP Address]
,bios.Version as [Bios Version]
,hw.Model
,hw.Manufacturer

FROM vComputer C
join Inv_SW_BIOS_Element bios on bios._ResourceGuid = c.Guid
join vHWComputerSystem hw on hw._ResourceGuid = c.Guid

SELECT
   [vri1_Computer].[Guid],
   [vri1_Computer].[Name]
FROM
   [vRM_Computer_Item] AS [vri1_Computer]
      LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca2_HW Logical Device]
         ON ([vri1_Computer].[Guid] = [dca2_HW Logical Device].[_ResourceGuid])
	  LEFT OUTER JOIN [Inv_SW_BIOS_Element] AS [BIOS]
         ON ([BIOS].[_ResourceGuid] = [vri1_Computer].[Guid])
WHERE
   (
      (
         ([dca2_HW Logical Device].[Model] LIKE N'Latitude 5480')
      )
	  AND
	  (
		 [BIOS].[Version] != '1.8.1'
	  )
   )

Hope this helps.

 

SELECT
   [vri1_Computer].[Guid],
   [vri1_Computer].[Name]
FROM
   [vRM_Computer_Item] AS [vri1_Computer]
      LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca2_HW Logical Device]
         ON ([vri1_Computer].[Guid] = [dca2_HW Logical Device].[_ResourceGuid])
	  LEFT OUTER JOIN [Inv_SW_BIOS_Element] AS [BIOS]
         ON ([BIOS].[_ResourceGuid] = [vri1_Computer].[Guid])
WHERE
   (
      (
         ([dca2_HW Logical Device].[Model] LIKE N'Latitude 5480')
      )
	  AND
	  (
		 [BIOS].[Version] != '1.8.1'
	  )
   )

 

left join Inv_SW_BIOS_Element bios on bios._ResourceGuid = c.Guid
left join vHWComputerSystem hw on hw._ResourceGuid = c.Guid

I had to do case statements to cleanup everything. Here is what I have for the moment, if you want to try it.
Will show ALL Machines and their bios version.

SELECT distinct t2.[Name] as 'Hostname', t2.[Client Date], t3.[Vendor], T3.[Name] AS 'Manufacture Model',
 Case     
  when T3.[Name] IN('10A8S0CP00','10A8S12901','10A8S2BF01','10A90013GE','10A9002VUS') then 'Lenovo M93-SFF'
  when T3.[Name] IN('10A6S06F00','10A6S0A90V','10A7003BUK') then 'Lenovo M93-CMT'
  when T3.[Name] IN('10AAS0K000','10AAS0K001','10AASOK000') then 'Lenovo M93-TINY'
  when T3.[Name] IN('2992E5U','32282Y2','32283C5','3228AD5','3228AD6') then 'Lenovo M92-CMT'
  when T3.[Name] IN('10A6S09F00','10A6S1KF00','10A6S2LU00','','') then 'Lenovo M93-CMT'
  when T3.[Name] IN('32273W9','3227AM8') then 'Lenovo M92-SFF'
  when T3.[Name] IN('7034E61','7034E62','7052A1G') then 'Lenovo M91-CMT'
  when T3.[Name] IN('7033H29') then 'Lenovo M91-SFF'
  when T3.[Name] IN('20EQS06K00') then 'Lenovo Thinkpad P50'
  when T3.[Name] IN('4158VX1','4158FQ3','4158EF3') then 'Lenovo D20'
  when T3.[Name] IN('2347A32','2347BY9','2347HT5','4223E96','42234A0','42234H9') then 'Lenovo D30'
  when T3.[Name] IN('20FES0K400') then 'IBM ThinkPad Yoga 260'
  when T3.[Name] IN('20C6CTO1WW') then 'IBM ThinkPad Edge E540'
  when T3.[Name] IN('20B20011US') then 'IBM ThinkPad Edge E545'
  when T3.[Name] IN('4180FL6') then 'IBM ThinkPad T420'
  when T3.[Name] IN('4223W1W','4223W23','43534U7') then 'IBM ThinkPad T430'
  when T3.[Name] IN('20B7S07J00','20B7S07J0A') then 'IBM ThinkPad T440'
  when T3.[Name] IN('20BUS01R00') then 'IBM ThinkPad T450'
  when T3.[Name] IN('2081CTO') then 'IBM ThinkPad T500'
  when T3.[Name] IN('20AVCTO1WW') then 'IBM ThinkPad L540'
  when T3.[Name] IN('20BG0016US') then 'IBM ThinkPad W540'
  when T3.[Name] IN('8183QU7','8183T7U','818343G') then 'IBM ThinkCentre S50'
  when T3.[Name] IN('-[621832U]-') then 'IBM IntelliStation M Pro'
  when T3.[Name] IN('11361k3','11361k4','11361k5','1097A56','1097A58','1097A57') then 'Lenovo ThinkStation C30'
  when T3.[Name] IN('1872wf7') then 'IBM ThinkPad T43'
  when T3.[Name] IN('1952bh2') then 'Lenovo ThinkPad T60'
  when T3.[Name] IN('7658CTO','76633FU') then 'Lenovo ThinkPad T61'
  when T3.[Name] IN('3093bf7','2985C7U') then 'Lenovo Thinkpad X201'
  when T3.[Name] IN('34371b0') then 'Lenovo ThinkPad X230'
  when T3.[Name] IN('7258A11') then 'Lenovo ThinkCentre M58e'
  when T3.[Name] IN('6138ar7','6137CT1','7635B52') then 'Lenovo ThinkCentre M58p'
  when T3.[Name] IN('8113da1','8114a93','8215DN5') then 'IBM ThinkCentre M52'
  when T3.[Name] IN('814334u','8143mgb') then 'IBM ThinkCentre M51'
  when T3.[Name] IN('8189K3G','8189NUM') then 'IBM ThinkCentre M50'
  when T3.[Name] IN('8816A22') then 'IBM ThinkCentre M55p'
  when T3.[Name] IN('8307LVU') then 'Lenovo NetVista'
  when T3.[Name] IN('945GM') then 'Intel 945GM Express'
  when T3.[Name] IN('C2SBC-Q','C7Q67','X9SRA/X9SRA-3','X8DTL','C7Z87-OCE','H8DG6/H8DGi') then 'Supermicro'
  when T3.[Name] IN('C2SBi') then 'Radisys'
  when T3.[Name] IN('CELSIUS M470-2') then 'Fujitsu Celsius M470'
  when T3.[Name] IN('CF-31SELAX1M','CF-31WELAXLM','CF-31WEUAXCE','CF-31WXLAXLM') then 'Panasonic Toughbook 31'
  when T3.[Name] IN('Compaq 100B SFF PC (Adina)') then 'Compaq 100B SFF'
  when T3.[Name] IN('D53427RK') then 'Intel NUC'
  when T3.[Name] IN('D915GUX_','DB43LD__','DG31PR','DH55TC__','DH67BL__','DP67BA__','DQ965GF_','DZ77BH') then 'Intel Desktop'
  when T3.[Name] IN('Dell DXP051') then 'Dell XPS 400'
  when T3.[Name] IN('ESPRIMO P900') then 'Fujitsu Esprimo P900'
  when T3.[Name] IN('ESPRIMO P920') then 'Fujitsu Esprimo P920'
  when T3.[Name] IN('Evo D510 CMT') then 'Compaq Evo D510'
  when T3.[Name] IN('IBM System X3250 M4 -[2583KDG]-') then 'IBM ExpSell x3250 M4 Rack'
  when T3.[Name] IN('IBM System x3500 M4 Server -[7383ZDM]-') then 'IBM ExpSell x3500 M4 Rack'
  when T3.[Name] IN('Z97X-UD3H-BK','Z97MX-Gaming 5','Z87X-D3H') then 'Gigabyte'
  when T3.[Name] IN('Vostro 420 Series') then 'Dell Vostro 420'
  when T3.[Name] IN('Vostro 230') then 'Dell Vostro 230'
  when T3.[Name] IN('Vostro 2420') then 'Dell Vostro 2420'
  when T3.[Name] IN('0385B95','0385CM8') then 'Lenovo M81'
  when T3.[Name] IN('6075A45','6086A16') then 'Lenovo M57'
  when T3.[Name] IN('9160A11') then 'Lenovo M57p'
  when T3.[Name] IN('2929CL6') then 'Lenovo M82'
  when T3.[Name] IN('0967B5U') then 'Lenovo M72e'
  when T3.[Name] IN('10DU0006UK') then 'Lenovo E73'
  when T3.[Name] IN('10AXS28600','10B6001SUS','10B6001XUS') then 'Lenovo M73'
  when T3.[Name] IN('2742A19','2742BG4','2929A33','2929CK8') then 'Lenovo M82'
  when T3.[Name] IN('10AH001HUS','10AGS0720A','10AHS2JL00','10AGS11D00','') then 'Lenovo M83'
  when T3.[Name] IN('30ATS0NK00') then 'Lenovo ThinkStation P310'
  when T3.[Name] IN('30B5S04000') then 'Lenovo ThinkStation P510'
  when T3.[Name] IN('30A9001DUS','30A8S15A00','30A8S00Q00','30A8S0NN00','30A8S0QC00','30A8S0S300','30A8S0US00','30A8S0WT00','30A8S1W000','30A8S21V00') then 'Lenovo ThinkStation P700'
  when T3.[Name] IN('30B6S09400','30B6S0DQ00','30B7000TUS','30B6S06500','30B7S04500','30B6S0QC00','30B6S0C700') then 'Lenovo ThinkStation P710'
  when T3.[Name] IN('10HY0020US','10GT002SUS') then 'Lenovo M700'
  when T3.[Name] IN('10FXS00Y01') then 'Lenovo M800'
  when T3.[Name] IN('10FCS04J0K','10FCS0S200','10FGS06B00','10FGS06B0Z','10FLS0B900','10FLS0B903','10FLS5EU01','10FGS3NR01','10FGS06B0P','10FCS2H801','10FCS2CW00','10FCS04J0S') then 'Lenovo M900'
  when T3.[Name] IN('10MUS0H20A','10MUS0H200') then 'Lenovo M910q-CMT'
  when T3.[Name] IN('10MNS16K0P','10MNS09U0F') then 'Lenovo M910t-SFF'
  when T3.[Name] IN('10MLS1SR00') then 'Lenovo M910s-Tiny'
  when T3.[Name] IN('30AH001JGE') then 'Lenovo ThinkStation P300'
  when T3.[Name] IN('4269A69','4269RP4') then 'Lenovo ThinkStation C20x'
  when T3.[Name] IN('43513E3') then 'Lenovo ThinkStation S30'
  when T3.[Name] IN('6181P-15A2MW71AC F') then 'Allen-Bradley Integrated Display'

 Else T3.[Name]
 End as 'True Model Name', t4.[Version] as 'BIOS Version', t6.[Total Physical Memory (Bytes)] / (1024 * 1024) as [RAM],
  t2.[OS Name], t2.[_ResourceGuid]
FROM [vComputer] T0
 LEFT JOIN [RMV_HW_Hardware] t1 on t0.[Guid] = t1.[Guid]
 LEFT JOIN [Inv_AeX_AC_Identification] t2 on t1.[Guid] = t2.[_ResourceGuid]
 LEFT JOIN [Inv_Win32_Computer_Systems_Product] t3 on t2.[_ResourceGuid] = t3.[_ResourceGuid]
 LEFT JOIN [vFixedAssetResourceStatus] t5 on t2._ResourceGuid = t5.Guid
 LEFT join vHWComputerSystem t6 on t2.[_ResourceGuid] = t6.[_ResourceGuid]
 LEFT join [Inv_SW_BIOS_Element] t4 on t2._resourceguid = t4._resourceguid
Where t5.Status = 'Active'
order by t3.Name