Month – May 2012

Find Programs Removed from a PC (updated)

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/db96da44-a9e0-4996-bca2-286b6e2c473a SELECT DISTINCT CS.Name0, HARP.Publisher0 AS ‘Publisher’, HARP.ProdID0 AS ‘Product’, HARP.DisplayName0 AS ‘Name’ FROM dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceID = CS.ResourceID join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on HARP.ResourceID = ARP.ResourceID WHERE ARP.DisplayName0<>HARP.DisplayName0 AND HARP.Publisher0 Is Not Null union SELECT DISTINCT CS.Name0, HARP.Publisher0 AS ‘Publisher’, HARP.ProdID0 AS ‘Product’, HARP.DisplayName0 AS ‘Name’ FROM […]

How to exclude a collection from sccm collection query

For full details, see this forums post. http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/75288b97-1fe6-4634-a20a-54091bba8d91   select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_SoftwareFile.FilePath like “%\\Program Files\\Internet Explorer\\” or SMS_G_System_SoftwareFile.FilePath like “%\\Program\\Internet Explorer\\”) and SMS_G_System_SoftwareFile.FileName = “iexplore.exe” and SMS_G_System_SoftwareFile.FileVersion like “7.%” and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_SMS000ES)

70-243

Well it is official, I wrote the Administering and Deploying System Center 2012 beta exam back in February. This morning, I finally got the email that says that I passed the exam. Woohoo.. 😉  

Adobe Dashboard

For full details see forum post http://social.technet.microsoft.com/Forums/en-US/configmgrsdk/thread/8d8cabc9-5b7b-477b-8443-c6c2abae64c2/#4b6c6e69-adee-4f6b-b51d-da941d1dcf41 Select left(DisplayName0,14), count(distinct ResourceID) as ‘client count’ from dbo.v_ADD_REMOVE_PROGRAMS ARP where ARP.DisplayName0 like ‘Adobe Reader%’ group by left(DisplayName0,14) order by left(DisplayName0,14)

List of PC with Project and Subnet

For full details, see posts: http://www.myitforum.com/forums/SCCM-report-m235714.aspx http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/07158c51-06e1-4afb-8a46-187eaf8e82eb   select Distinct CS.Name0 from dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_Add_Remove_Programs ARP on CS.ResourceID = ARP.ResourceID join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceID = NAC.ResourceID Where ARP.DisplayName0 like ‘%Project%’ and (IPAddress0 like ‘10.1.%’ or IPAddress0 like ‘10.4.%’)