none
Lenovo Warranty Lookup with SCCM SQL

    Question

  • Anyone have any luck pulling Lenovo Warranty information with this?

     

    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 '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 '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 'IBM%'

    THEN 'http://www-304.ibm.com/systems/support/supportsite.wss/warranty?type=' + LEFT(REPLACE(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_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 = @variable

    AND v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12

    It looks like Lenovo now uses this site:  :  http://support.lenovo.com/en_US/product-service/warranty-status/default.page?

    Sincerly,

    Chris

    Monday, October 10, 2011 4:36 PM

Answers

  • Yes but we are also adding the prompt for the PC name too. i.e. @variable 


    http://www.enhansoft.com/
    • Marked as answer by Massara Friday, December 16, 2011 4:12 PM
    Thursday, December 15, 2011 8:49 PM
  • Just remove the whole Where statement of the query. This will give you everything within your db.


    http://www.enhansoft.com/
    • Marked as answer by Massara Friday, December 16, 2011 7:17 PM
    Friday, December 16, 2011 6:49 PM

All replies

  • The actual warranty page was embedded as an iFrame.  The below link appears to work correctly for me:

    http://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=*MachineType*&sysSerial=*Serial*


    Scott Gill
    SCCM Consultant
    Tuesday, October 11, 2011 2:48 PM
  • Scott,

    May I asked what your sql statement looklike?

     

     

    Tuesday, October 11, 2011 5:40 PM
  • I've never actually used this approach I just found the site for you... That said, this should do the trick for the SQL statement (untested).

     

    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 '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 '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 'IBM%'
    
    THEN 'http://www-304.ibm.com/systems/support/supportsite.wss/warranty?type=' + LEFT(REPLACE(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://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=' + v_gs_computer_system.model0 + '&sysSerial=' + v_GS_PC_BIOS.serialnumber0
    
    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_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 = @variable
    
    AND v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12 
    



    Scott Gill
    SCCM Consultant
    Tuesday, October 18, 2011 1:23 PM
  • Thanks for the help Scott.

    I am very close. This time with the query above it doesn't say Not Found for Warranty.

    It points me to this site: http://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=874255U&sysSerial=L3C0308  which now include serial number of L3C0308.

    Now if I can only figure out how to include Machine type cause the crazy lenovo site requires machine type.

     

    Wednesday, October 19, 2011 1:20 PM
  • I swear it worked the first time when I put the full machine type in that site... Apparently the issue here is that it expects only the first 4 characters of Machine Type.  Give this a try:

     

    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 '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 '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 'IBM%'
    
    THEN 'http://www-304.ibm.com/systems/support/supportsite.wss/warranty?type=' + LEFT(REPLACE(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://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=' + LEFT(v_gs_computer_system.model0, 4) + '&sysSerial=' + v_GS_PC_BIOS.serialnumber0
    
    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_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 = @variable
    
    AND v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12

    Scott Gill
    SCCM Consultant
    • Proposed as answer by David Komlosy Wednesday, June 13, 2012 10:13 PM
    Wednesday, October 19, 2011 7:34 PM
  • Yea still no luck.

    Ofcourse I contacted our Lenovo Contact and so far it hasn't been fruitful.

     

    Thursday, October 20, 2011 7:11 PM
  • Yes I know this is an old post, I’m trying to clean up all un-answered post.

    Did you figure this out, if you how?


    http://www.enhansoft.com/
    Saturday, December 10, 2011 4:38 PM
  • No sir  ; (
    Tuesday, December 13, 2011 2:27 PM
  • So what did Lenovo rep say? Did you talk to the SE?
    http://www.enhansoft.com/
    Wednesday, December 14, 2011 3:15 AM
  • Basically were not helpful yeap multiple SE.  One even told me he never heard of SCCM.
    Wednesday, December 14, 2011 2:18 PM
  • BTW, the link you provided above work for me. I see warranty data, using it. http://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=874255U&sysSerial=L3C0308
    http://www.enhansoft.com/
    Wednesday, December 14, 2011 2:51 PM
  • Using the script above? Hmmm
    Wednesday, December 14, 2011 3:10 PM
  • I didn't use the scritp above, I just clicked the link above. I assumed that is what you are getting from the script, particularly since it looks right.
    http://www.enhansoft.com/
    Wednesday, December 14, 2011 3:29 PM
  • Yea I use that lenovo link inside that sql query and no luck.
    Wednesday, December 14, 2011 10:10 PM
  • ok, so I try the query in Scott's post and it works as is. So I have no idea as to what is going with your setup.

    What exactly is the issue? What error message are you getting?


    http://www.enhansoft.com/
    Wednesday, December 14, 2011 11:19 PM
  • LOL really so you just copy and paste his code and it worked.

    Argg, hmm

    Thursday, December 15, 2011 2:29 PM
  • yup that is all I did.
    http://www.enhansoft.com/
    Thursday, December 15, 2011 2:38 PM
  • This is what I get:

     HOU-59934   hou-59934\administrator   R9GBMHB   No Asset Information   R9GBMHB   LENOVO   42434UU   (Not Available)   1   http://support.lenovo.com/templatedata/Web%20Content/JSP/warrantyLookup.jsp?sysMachType=4243&sysSerial=R9GBMHB 

    Basically just a link to Lenovo it doesn't get the data from lenovo and post it.

     

    Thursday, December 15, 2011 7:56 PM
  • You guys are just copying and pasting the query?  I'm getting an error, Must declare the scalar variable "@variable".  Massara - definitely would be interested to know if you get this to work.  We are an all Lenovo shop and this would be very useful.
    Thursday, December 15, 2011 8:43 PM
  • Ya that all the query does, it give you the link so that you can manually check the warranty information on each PC.

     

    If you want the warranty data within CM07 then you will need to look at 3<sup>rd</sup> party add-ons to do that.


    http://www.enhansoft.com/
    Thursday, December 15, 2011 8:47 PM
  • Yes but we are also adding the prompt for the PC name too. i.e. @variable 


    http://www.enhansoft.com/
    • Marked as answer by Massara Friday, December 16, 2011 4:12 PM
    Thursday, December 15, 2011 8:49 PM
  • I'd love to even just have the link to click on.  I guess I'll play around with the script and try to figure it out.  Thanks Garth.
    Thursday, December 15, 2011 9:10 PM
  • Stupid question but when you add the report SQL, are you clicking the prompt… button, then clicking the star(*), then typing variable in the first two text boxes?


    http://www.enhansoft.com/
    Thursday, December 15, 2011 9:21 PM
  • Not a stupid question.  I'm new to this, and no, I wasn't typing variable in the first two boxes.  It took the SQL query, but I'm not seeing any data.  I'll play around some more, I'm not trying to hijack this thread. :/
    Thursday, December 15, 2011 9:28 PM
  • If you ever get a variable error that means you need a prompt, at least thats my rule.

    Oh I see Garth so mine is working just have to click on the link! Great there goes my lazy, oh nevermind.

     

    LOL

     

    Merry Christmas!

    Friday, December 16, 2011 4:13 PM
  • Garth,

    One last question on this topic, may I asked how do update this query above so that it will list all my machines in one page.

    My plan is to just give them my report page link of all the machines and on the right they click the Warranty Page. We have about 1600 Lenovo machines.

     

    Thanks

    Friday, December 16, 2011 4:42 PM
  • Just remove the whole Where statement of the query. This will give you everything within your db.


    http://www.enhansoft.com/
    • Marked as answer by Massara Friday, December 16, 2011 7:17 PM
    Friday, December 16, 2011 6:49 PM
  • Perfect!  Thanks as always!
    Friday, December 16, 2011 7:17 PM
  • AWESOME!  That is exactly what I was looking for as well!  Thank you both so much.
    Friday, December 16, 2011 8:07 PM
  • Have you ever seen this blog post ? similar to Scott's query? http://myitforum.com/cs2/blogs/tom_watson/archive/2011/06/03/system-warranty-report.aspx

    Anoop C Nair - @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    User Group:  ConfigMgr Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Friday, December 30, 2011 12:30 PM