Wednesday, June 8, 2011

SMS Report to find machines with duplicate serial numbers

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

3 Comments:

Anonymous Anonymous said...

these queries are great...
but can you provide a detail way of doing this...where to add or how to run these queries...
I am new to sccm and would like to know how we can see the details of the advertised programs as some of the clients receives the advertisement within 2-3 min and some takes long time for this is there a way by which i can track the process which are going on after the advertisement is made...

thanks & regards,
pranay bhagat

June 17, 2011 at 5:11 AM  
Blogger Jaison Jose said...

you can run these queries against your SMS/SCCM database (SQL Query Analyzer/SQL Management Studio). Or you can create this as reports in your SMS/SCCM console.

Regarding the advertisments, you may see my below post.

http://jj-with-mad-skillz.blogspot.com/2010/09/query-to-find-detailed-advertisement.html

June 17, 2011 at 5:57 AM  
Anonymous Anonymous said...

Congratulations! Has been helpful for me!

thanks!

December 20, 2011 at 11:42 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home