The Problem

The other day I was faced with a simple little issue to solve.  I had the Serial Number/Service Tag for a Dell server but I had no idea where it was physically located nor did I know the name of the server.  This would have not been a problem with our clients because we name our clients the same as the Service Tag.  But, our servers are named differently.

System Center Configuration Manager to the Rescue…Again!

So I decided to use SCCM reporting to solve this problem since SCCM collects all sorts of data anyway.  I created a custom report called Computers with a specific Dell ServiceTag.  The following is the SQL statement that I wrote.

SELECT SYS.Netbios_Name0, SYS.AD_Site_Name0, SYS.Operating_System_Name_and0
FROM v_GS_PC_BIOS BIOS
JOIN v_R_System SYS on SYS.ResourceID = BIOS.ResourceID
WHERE BIOS.SerialNumber0 LIKE @serialNumber
ORDER BY SYS.Netbios_Name0

  1. Click the Prompts button and add a prompt property
  2. Name: serialNumber and Prompt text: Serial Number
  3. Check the Provide a SQL statement box and enter the following:

    begin
    if (@__filterwildcard = '')
    select distinct SerialNumber0 from v_GS_PC_BIOS order by SerialNumber0
    else
    select distinct SerialNumber0 from v_GS_PC_BIOS
    WHERE SerialNumber0 like @__filterwildcard
    order by SerialNumber0
    end

That should do it.