-- sysDrivers.sql
-- Version 1.2
-- Copyright 2010 Ryan McLean (ryan1_00 !at! hotmail !dot! com )
-- Gets Dell systems and creates links to their driver download pages
-- Known Issues:
-- * When the page loads the Info on the page can be incorrect however the table with the drivers IS correct Refresh the page to correct. 
-- * If os isn't supported for the system by dell one that is will be displayed.
-- * Assumes Chassis Types 23 & 17 are servers and all others are not.

-- Declare Variables --
DECLARE 
@w764 nvarchar(5), @w732 nvarchar(5), @wv64 nvarchar(5), 
@wv32 nvarchar(5), @xp64 nvarchar(5), @xp32 nvarchar(5), 
@w2k nvarchar(5), 
@w2k332 nvarchar(5), @w2k364 nvarchar(5), @w2k832 nvarchar(5),
@w2k864 nvarchar(5), @w2k8R2 nvarchar(5);

-- Initialize Variables --
-- User Systems
SET @w764 = 'W764'; -- Win7 x64
SET @w732 = 'W732'; -- Win7 x86
SET @wv64 = 'WV64'; -- Vista x64
SET @wv32 = 'WLH';  -- Vista x32
SET @xp64 = 'WXPX'; -- XP x64
SET @xp32 = 'WW1';  -- XP x86
SET @w2k  = 'WNT5'; -- Windows 2000
-- Server Systems
SET @w2k332 = 'WNET'; -- Windows Server 2003
SET @w2k364 = 'WX64'; -- Windows Server 2003 x64
SET @w2k832 = 'WLHS1'; -- Windows Server 2008 x86
SET @w2k864 = 'LHS64'; -- Windows Server 2008 x64
SET @w2k8R2 = 'WS8R2'; -- Windows Server 2008 R2

SELECT DISTINCT
resourceSys.netbios_name0                 AS "Computer Name",
v_gs_system_console_usage.topconsoleuser0 AS "Top Console User",
vgsos.Caption0                            AS "Operating System",
resourceArchProj.arch                     AS "OS Architecture",
v_gs_system_enclosure.serialnumber0       AS "Serial Number",
v_gs_system_enclosure.smbiosassettag0     AS "Asset Tag",
v_GS_PC_BIOS.serialnumber0                AS "PC Bios Serial Number",
v_gs_computer_system.model0               AS "Computer Model",
CASE v_gs_system_enclosure.ChassisTypes0       
  WHEN 1 then 'Other'
  WHEN 2 then 'Unknown'
  WHEN 3 then 'Desktop'
  WHEN 4 then 'Low Profile Desktop'
  WHEN 5 then 'Pizza Box'
  WHEN 6 then 'Mini Tower'
  WHEN 7 then 'Tower'
  WHEN 8 then 'Portable'
  WHEN 9 then 'Laptop'
  WHEN 10 then 'Notebook'
  WHEN 11 then 'Hand Held'
  WHEN 12 then 'Docking Station'
  WHEN 13 then 'All in One'
  WHEN 14 then 'Sub Notebook'
  WHEN 15 then 'Space-Saving'
  WHEN 16 then 'Lunch Box'
  WHEN 17 then 'Main System Chassis'
  WHEN 18 then 'Expansion Chassis'
  WHEN 19 then 'SubChassis'
  WHEN 20 then 'Bus Expansion Chassis'
  WHEN 21 then 'Peripheral Chassis'
  WHEN 22 then 'Storage Chassis'
  WHEN 23 then 'Rack Mount Chassis'
  WHEN 24 then 'Sealed-Case PC'
  ELSE 'Unknown'
END AS "Chassis Type",
CASE 
  WHEN (vgsos.Caption0 LIKE 'Microsoft Windows Server 2008 R2%') -- Server 2008 R2
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k8R2 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft® Windows Server® 2008%') AND (resourceArchProj.arch = 'x64') -- Server 2008 x64
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k864 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft® Windows Server® 2008%') AND (resourceArchProj.arch = 'x86') -- Server 2008 x86
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k832 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft(R) Windows(R) Server 2003%') AND (resourceArchProj.arch = 'x64') -- Server 2003 x64
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k364 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft(R) Windows(R) Server 2003%') AND (resourceArchProj.arch = 'x86') -- Server 2003 x86
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k332 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft Windows 2000%') -- Windows 2000
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft Windows 7%') AND (resourceArchProj.arch = 'x64') -- Windows 7 x64
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w764 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft Windows 7%') AND (resourceArchProj.arch = 'x86') -- Windows 7 x86
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w732 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE '%Vista%') AND (resourceArchProj.arch = 'x64') -- Windows Vista x64
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @wv64 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE '%Vista%') AND (resourceArchProj.arch = 'x86') -- Windows Vista x86
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @wv32 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE '% XP %') AND (resourceArchProj.arch = 'x64') -- Windows XP x64
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @xp64 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE '% XP %') AND (resourceArchProj.arch = 'x86') -- Windows XP x86
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @xp32 + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN (vgsos.Caption0 LIKE 'Microsoft Windows 2000%') -- Windows 2000
    THEN 'http://support.dell.com/support/downloads/driverslist.aspx?c=us&l=en&s=gen&catid=-1&dateid=-1&formatid=-1&hidlang=en&impid=-1&scanConsent=False&scanSupported=False&typeid=-1&TabIndex=&osl=EN&os=' + @w2k + '&servicetag=' + v_gs_system_enclosure.serialnumber0
  ELSE 'Unknown'
END AS "Driver Link"
FROM   v_gs_processor 
  INNER JOIN (
    SELECT 
      resourceid, 
      netbios_name0 
    FROM v_r_system
  ) resourceSys ON v_gs_processor.resourceid = resourceSys.resourceid
  INNER JOIN v_GS_OPERATING_SYSTEM vgsos ON vgsos.ResourceID = resourceSys.ResourceID
  INNER JOIN v_gs_system_enclosure 
    ON v_gs_system_enclosure.resourceid = resourceSys.resourceid 
  INNER JOIN (
    SELECT
      ResourceID,
      CASE
        WHEN v_gs_system.SystemType0 LIKE 'x86%'
          THEN 'x86'
        WHEN v_gs_system.SystemType0 LIKE 'x64%'
          THEN 'x64'
      END AS arch
    FROM v_gs_system
  ) resourceArchProj ON resourceArchProj.ResourceID = resourceSys.resourceid 
  INNER JOIN v_GS_PC_BIOS 
    ON v_GS_PC_BIOS.resourceid = resourceSys.resourceid 
  INNER JOIN v_gs_computer_system 
    ON (v_gs_computer_system.resourceid = resourceSys.resourceid) 
  LEFT JOIN v_gs_system_console_usage 
    ON v_gs_system_console_usage.resourceid = resourceSys.resourceid 
  LEFT JOIN v_lu_cpu 
    ON Lower(v_lu_cpu.cpuhash) = Lower(v_gs_processor.cpuhash0)
WHERE v_gs_system_enclosure.ChassisTypes0 <> 12 -- Exclude Docking stations
AND v_gs_computer_system.manufacturer0 LIKE '%DELL%'