Duplicate serial numbers in SCCM report. Need to filter out

Question

Hello,

Due to historical data, I am trying to write a report that filters out duplicate serial numbers that match two machine names. I think I would like to filter by the field "Last Activity" that I am using.

So, what I would like to do is probably an "IF statement" that says, If the serial number has a duplicate, display the result that has the greater "Last Activity" date.

Here is what I am using:

select distinct

v_GS_PC_BIOS.SerialNumber0 AS SerialNumber,
v_R_System.Name0 AS Hostname,
v_R_System.Distinguished_Name0 AS ADContainer,
v_R_System.User_Name0 AS LastUser,
v_R_System.Affinity_Full_Name0 AS AffinityUser,
v_R_User.Mail0 AS EmailLU,
v_R_User.telephonenumber AS PhoneLU,
v_CollectionMemberClientBaselineStatus.LastActiveTime AS LastCommunication,
CASE v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
when 8 THEN 'Laptop'
when 9 THEN 'Laptop'
when 10 THEN 'Laptop'
when 14 THEN 'Laptop'
when 15 THEN 'Desktop'
when 21 THEN 'Laptop'
When 3 THEN 'Desktop'
When 4 THEN 'Desktop'
When 6 THEN 'Desktop'
When 7 THEN 'Desktop'
When 13 THEN 'Desktop'
When 12 THEN 'Docking Station'
when 16 THEN 'Lunchbox'
When 32 THEN 'Tablet_Conv'
END as "Chassis Type",
v_GS_COMPUTER_SYSTEM.Model0 AS "Model"

from v_GS_PC_BIOS

left JOIN v_R_System ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
left JOIN v_r_user ON v_r_user.User_Name0 = v_R_System.User_Name0
left JOIN v_GS_NETWORK_LOGIN_PROFILE ON v_R_System.ResourceID = v_GS_NETWORK_LOGIN_PROFILE.ResourceID
left JOIN v_CollectionMemberClientBaselineStatus ON v_GS_NETWORK_LOGIN_PROFILE.ResourceID = v_CollectionMemberClientBaselineStatus.MachineID
left join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_PC_BIOS.ResourceID
left join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID

where v_GS_PC_BIOS.SerialNumber0 LIKE @SerialNumber and
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 not like '12'

Answer 1

@My9to5
Thank you for posting in Microsoft Q&A forum.
You may try to use below query to find machines with duplicate serial numbers, how many duplicate serial numbers do you have?

SELECT  
a.Name0,  
a.UserName0,  
d.sms_installed_sites0 as 'Site Code',  
e.Lasthwscan as 'Last hardware Scan',  
repeat.SerialNumber0  
FROM  
v_GS_COMPUTER_SYSTEM a,v_r_system c,v_RA_System_SMSInstalledSites d,v_GS_WORKSTATION_STATUS e,  
v_GS_PC_BIOS b,  
(SELECT b.SerialNumber0 FROM v_GS_PC_BIOS b GROUP BY  
b.SerialNumber0 HAVING (Count(b.SerialNumber0)>1)) as  
repeat  
WHERE  
b.ResourceID = a.ResourceID  
and a.ResourceID=c.resourceid  
and b.resourceid=d.resourceid  
and b.resourceid=e.resourceid  
and b.SerialNumber0 = repeat.SerialNumber0  
and (c.client0=1 and c.obsolete0=0 and c.Active0=1)  
order by  
b.SerialNumber0,  
a.Name0,  
a.UserName0  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.