Month – May 2012

Find Programs Removed from a PC (updated)

For full details see: 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.   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)


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 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:   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.%’)