SHARE
TWEET

Untitled

a guest May 19th, 2017 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT DISTINCT
  2.     CS.Name0 as 'PC',
  3.     BIOS.SerialNumber0 as 'SN #',
  4.     CS.Manufacturer0 as 'Manufacturer',
  5.     CS.Model0 as 'Model',
  6.     OS.Caption0 as 'OS',
  7.     OS.CSDVersion0 as 'Service Pack',
  8.     SCUM.TopConsoleUser0 as 'Top User',
  9.     R.User_Name0 AS 'Last Logged (Heartbeat)',
  10.     CS.UserName0 AS 'Last Logged (HW Inv.)',
  11.  
  12.  
  13.  
  14.     Case SE.ChassisTypes0    
  15.         when '1' then 'Other'
  16.         when '2' then 'Unknown'
  17.         when '3' then 'Desktop'
  18.         when '4' then 'Low Profile Desktop'
  19.         when '5' then 'Pizza Box'
  20.         when '6' then 'Mini Tower'
  21.         when '7' then 'Tower'
  22.         when '8' then 'Portable'
  23.         when '9' then 'Laptop'
  24.         when '10' then 'Notebook'
  25.         when '11' then 'Hand Held'
  26.         when '12' then 'Docking Station'
  27.         when '13' then 'All in One'
  28.         when '14' then 'Sub Notebook'
  29.         when '15' then 'Space-Saving'
  30.         when '16' then 'Lunch Box'
  31.         when '17' then 'Main System Chassis'
  32.         when '18' then 'Expansion Chassis'
  33.         when '19' then 'SubChassis'
  34.         when '20' then 'Bus Expansion Chassis'
  35.         when '21' then 'Peripheral Chassis'
  36.         when '22' then 'Storage Chassis'
  37.         when '23' then 'Rack Mount Chassis'
  38.         when '24' then 'Sealed-Case PC'
  39.         else 'Undefinded'
  40.     end as 'PC Type'
  41. FROM
  42.     dbo.v_R_System R
  43.     INNER JOIN dbo.v_GS_PC_BIOS BIOS ON R.ResourceID = BIOS.ResourceID
  44.     INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID
  45.     INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID
  46.     LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID
  47.     INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
  48.     INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE on R.ResourceID = SE.ResourceID
  49.  
  50.  
  51. WHERE
  52.  
  53.     S.SystemRole0 = 'Workstation'
  54.     AND SE.ChassisTypes0 in ( '8', '9', '10', '11', '12', '14', '18', '21' )
  55. ORDER BY
  56.     CS.Name0
RAW Paste Data
Challenge yourself this year...
Learn something new in 2017
Top