May 2012 - Posts

Find Programs Removed from a PC (updated)
Monday, May 21, 2012 10:15 AM

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 
	dbo.v_GS_COMPUTER_SYSTEM CS
	join dbo.v_GS_ADD_REMOVE_PROGRAMS_64 ARP on ARP.ResourceID = CS.ResourceID 
	join dbo.v_HS_ADD_REMOVE_PROGRAMS_64 HARP on HARP.ResourceID = ARP.ResourceID 
WHERE 
	ARP.DisplayName0<>HARP.DisplayName0 
	AND HARP.Publisher0 Is Not Null

by Garth Jones

Filed under: ConfigMGr, System Center, Code, SQL, SCCM, vNext, CM12, CM07

How to exclude a collection from sccm collection query
Saturday, May 19, 2012 10:47 AM

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)

by Garth Jones

Filed under: ConfigMGr, System Center, Code, WQL, SCCM

70-243
Wednesday, May 16, 2012 12:51 PM

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.. ;-)

 

by Garth Jones

LOL OOO message
Tuesday, May 15, 2012 7:22 PM

Out of Office funny.

The funny part about this out of office message, they sent me an email yesterday and they wanted me to reply yesterday!  Go figure. (I replied today )

 

by Garth Jones

Filed under: Fun Links

Adobe Dashboard
Sunday, May 13, 2012 11:06 AM

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)

by Garth Jones

List of PC with Project and Subnet
Thursday, May 03, 2012 8:04 AM

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.%')

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL, SCCM