|
MeenEnta -> RE: Report on age of desktop/laptop hardware (10/1/2008 11:08:33 AM)
|
Thx Tom for Adding the Lonovo / Gateway, but we were able to get the Dell links for Warranty to work, here's the code: changes in red.
SELECT DISTINCT v_r_system.netbios_name0 AS "Computer Name",
v_gs_system_console_usage.topconsoleuser0 AS "Top Console User",
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.manufacturer0 AS "Computer Manufacturer",
v_gs_computer_system.model0 AS "Computer Model",
v_GS_OPERATING_SYSTEM.InstallDate0 AS "Install Date (Cloned)",
"Estimated Date of CPU Manufacture" = CASE
WHEN (CAST(v_lu_cpu.cpu_birth AS VARCHAR) IS NULL) THEN '(Not Available)'
ELSE CAST(v_lu_cpu.cpu_birth AS VARCHAR)
END,
ismulticore0 AS "Is Multi Processor System",
CASE
WHEN v_gs_computer_system.manufacturer0 LIKE "Hewlett%" THEN 'http://h20000.www2.hp.com/bizsupport/TechSupport/WarrantyResults.jsp?sn=' + v_gs_system_enclosure.serialnumber0 + '&country=US'
WHEN v_gs_computer_system.manufacturer0 LIKE "HP%" THEN 'http://h20000.www2.hp.com/bizsupport/TechSupport/WarrantyResults.jsp?sn=' + v_gs_system_enclosure.serialnumber0 + '&country=US'
WHEN v_gs_computer_system.manufacturer0 LIKE "Compaq%" THEN 'http://h20000.www2.hp.com/bizsupport/TechSupport/WarrantyResults.jsp?sn=' + v_gs_system_enclosure.serialnumber0 + '&country=US'
WHEN v_gs_computer_system.manufacturer0 LIKE "Dell%" THEN 'http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?c=us&l=en&s=gen&servicetag=' + v_gs_system_enclosure.serialnumber0
WHEN v_gs_computer_system.manufacturer0 LIKE "IBM%" THEN 'http://www-304.ibm.com/systems/support/supportsite.wss/warranty?type=' + LEFT (v_gs_computer_system.model0, 4) + '&serial=' + v_GS_PC_BIOS.serialnumber0 + '&action=warranty&brandind=5000008'
ELSE '(Not available)'
END AS "Warranty Information"
FROM v_gs_processor
INNER JOIN v_r_system
ON v_gs_processor.resourceid = v_r_system.resourceid
INNER JOIN v_GS_OPERATING_SYSTEM
ON v_gs_processor.resourceid = v_GS_OPERATING_SYSTEM.resourceid
INNER JOIN v_gs_system_enclosure
ON v_gs_system_enclosure.resourceid = v_r_system.resourceid
INNER JOIN v_GS_PC_BIOS
ON v_GS_PC_BIOS.resourceid = v_r_system.resourceid
INNER JOIN v_gs_computer_system
ON (v_gs_computer_system.resourceid = v_r_system.resourceid)
LEFT JOIN v_gs_system_console_usage
ON v_gs_system_console_usage.resourceid = v_r_system.resourceid
LEFT JOIN v_lu_cpu
ON Lower(v_lu_cpu.cpuhash) = Lower(v_gs_processor.cpuhash0)
WHERE v_r_system.netbios_name0 like @Name
AND v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12
|
|
|
|
