SCCM SQL Query - ComputerName to SerialNumber Comparison

Naming computers utilizing the serial number, a common practice.  A recent client needed to get a quick visual of which computers did not have the serial number in the computer name.  SQL to the rescue.

This SQL query below will do a comparison of the serial number and computer name and only show results of computers that do not match.  The client utilizes the front most characters of a computer name for location codes so we used a wildcard in the "where" statement as it could start with anything, but the last characters of the computer name must end in the serial number.

------------------------------

select 
   RS.Name0 as [System Name], 
    SerialNumber0 as [Serial Number],
    
from 
    v_GS_SYSTEM_ENCLOSURE SE
    Inner Join v_R_System RS
ON RS.ResourceID = SE.ResourceID

where RS.Name0 NOT LIKE '%' + se.SerialNumber0

order by 'System Name'


------------------------------

Now the client had some extra particulars.  They wanted to exclude all virtual machines, only include machines which Operating System was Windows 10, and specifically only grab the right-most 11 characters from the serial number.  HP serials are roughly 10 characters but Surface Pro 4 devices have 12 character serials.  Below we used the a similar query but you'll see for the serial number we added "right(se.SerialNumber0, 11)" to have it do the comparison on the right-most 11 characters.  You can change that for whichever particulars you might have in your environment such as "left(se.SerialNumber0, 5)" for the first 5 characters in a serial number and so on.

------------------------------

select 

   RS.Name0 as [System Name], 

    SerialNumber0 as [Serial Number],

    Operating_System_Name_and0 as [OS]

from 

    v_GS_SYSTEM_ENCLOSURE SE

    Inner Join v_R_System RS

ON RS.ResourceID = SE.ResourceID

where rs.Operating_System_Name_and0 LIKE '%10%' 

AND rs.Is_Virtual_Machine0 != 1 

AND RS.Name0 NOT LIKE '%' + right(se.SerialNumber0, 11) 

order by 'System Name'

------------------------------

You can customize this further for additional details you might like in this report such as the make/model of the device.  From here you can easily add this into a SSRS report and schedule it to email the report on a monthly basis.  Enjoy.