Retrieving Dell Service Tags using System Center Configuration Manager 2007 R2

I needed a list of the Dell service tags from all our desktops and laptops.  Dell have a product called the Dell Management Console (DMC) http://dell.symantec.com/dmc-tech which can do this, but as we have System Center Configuration Manager 2007 R2 (SCCM) I wanted to use that.  Below is the SQL I used to create the report in SCCM.  The important column is v_GS_PC_BIOS.SerialNumber0

select  distinct v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
LastBootUpTime0,v_GS_lastsoftwarescan.LastScanDate,v_GS_PC_BIOS.SerialNumber0 AS [Service Tag]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
join v_GS_lastsoftwarescan on  v_GS_lastsoftwarescan.resourceid = v_GS_OPERATING_SYSTEM.ResourceID
join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
and (lower(v_R_System_Valid.Netbios_Name0) like @ComputerName or @ComputerName=”)
and (v_R_System_Valid.Resource_Domain_OR_Workgr0 = @Domain or @Domain=”)
and (v_GS_OPERATING_SYSTEM.Caption0 = @OperatingSystem or @OperatingSystem=”)
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = @Manufacturer or @Manufacturer = ”)
and (v_GS_COMPUTER_SYSTEM.Model0=@Model or @Model = ”)
Order by v_R_System_Valid.Netbios_Name0

Advertisements