I'm sure most of us have the annoyance of trying to find out Warranty information. So here are some goodies if running through Evergreening your desktops.
I do have a question though, what's the fastest way to modify this report to look up by collection instead of individual PC's. Anytime I end up doing a quick modification on any of the reports I've built I tend to break them by missing things like ' or , or well...the usual PEBKAC errors?
Any dirty tricks a reporting pro can share out there?
---cut here---
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',
'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?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'
WHEN v_gs_computer_system.manufacturer0 LIKE 'Lenovo%' 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'
WHEN v_gs_computer_system.manufacturer0 LIKE 'Gateway%' THEN 'http://support.mpccorp.com/apps/complist.asp?SerialNo=GTW' + v_GS_PC_BIOS.serialnumber0
WHEN v_gs_computer_system.manufacturer0 LIKE 'Northern%' THEN 'http://www.nmicro.ca/en/support/driver-warranty/driverdisplay.asp?SNUM='+ v_GS_PC_BIOS.serialnumber0
WHEN v_gs_computer_system.manufacturer0 Like 'TOSHIBA' THEN 'http://toshiba.eclaim.com/toshiba/tsbclok2.asp?SelectModel='+dbo.v_GS_Client0.Version0 +'&SelectSerial='+ v_GS_PC_BIOS.serialnumber0
+'&pCallFrom=MAIN'
ELSE '(Not available)'
END AS 'Warranty Information'
FROM dbo.v_gs_processor
INNER JOIN dbo.v_r_system
ON v_gs_processor.resourceid = v_r_system.resourceid
INNER JOIN dbo.v_gs_system_enclosure
ON v_gs_system_enclosure.resourceid = v_r_system.resourceid
INNER JOIN dbo.v_GS_PC_BIOS
ON v_GS_PC_BIOS.resourceid = v_r_system.resourceid
INNER JOIN dbo.v_gs_computer_system
ON (v_gs_computer_system.resourceid = v_r_system.resourceid)
LEFT JOIN dbo.v_gs_system_console_usage
ON v_gs_system_console_usage.resourceid = v_r_system.resourceid
LEFT JOIN dbo.v_lu_cpu
ON Lower(v_lu_cpu.cpuhash) = Lower(v_gs_processor.cpuhash0)
LEFT JOIN dbo.v_GS_Client0
ON dbo.v_GS_Client0.resourceid = v_r_system.resourceid
WHERE v_r_system.netbios_name0 = @Name
AND v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12
Other recent topics
