Garth Jones

Find Programs Removed from a PC (updated)

Garth — Mon, 21 May 2012 14:15:59 GMT

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

How to exclude a collection from sccm collection query

Garth — Sat, 19 May 2012 14:47:37 GMT

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

Garth — Wed, 16 May 2012 16:51:00 GMT

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

LOL OOO message

Garth — Tue, 15 May 2012 23:22:31 GMT

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 )

Adobe Dashboard

Garth — Sun, 13 May 2012 15:06:41 GMT

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

Garth — Thu, 03 May 2012 12:04:53 GMT

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

MMS 2012

Garth — Mon, 30 Apr 2012 13:01:29 GMT

Well MMS 2012 is over and it has been crazy for me. Here is the summary of what happened:

My passport expired so had to get a new one, I picked it up on Thursday just before I left for Vegas
I know my luggage would be close to being overweight, so I didn’t bring a few things like shaving cream. I’m glad Walgreens is right there.
I arrive at the airport and find out my luggage weight 49.8 lbs., Max is 50 lbs.!

Luggage:

So I know that a few people ask what the deal was with my luggage and it was so heavy. Well you would think that it was because I over packed or something like that. Nope, thanks to tips for both Ed and Rory over the years, I have slowly learn to not over pack. So what was it?
2-1L bottles of Maple syrup from Stanley’s farm, Your welcome Kenny.
2-750 ml bottle of Rum. Long story.
Both weight in a 14 lbs.!

I had no problems with either US Customs or my flights!!! <knock on wood>
Booth looked great
The Pink Shirt, do I need to say any more. Lara got over 200 Tweets about this!
MIF party as always, it was great
Peter D. won our TV.
The Meet and Geek was cool
The closing party was good, I would say it ranks as the second best in MMS history
Me with Wally
Winning the CM12 Expert Quiz was nice but betting Wally was better. Here is the trophy!
One last tidbit, my luggage was 50.5 lbs. returning home, go figure!

Report Machine's Platform information

Garth — Thu, 05 Apr 2012 17:53:08 GMT

For full details see the forum post: http://www.myitforum.com/forums/Query-to-report-Machines-Platform-information-m235322.aspx

Select 
  Name0,
  SystemType0
From
  dbo.v_GS_SYSTEM

Query to report OU information for Machines

Garth — Thu, 05 Apr 2012 17:49:14 GMT

For full details please see:

http://www.myitforum.com/forums/query-to-report-OU-information-for-Machines-m235317.aspx

select 
  R.Name0,
  max(OU.System_OU_Name0)
From
  dbo.v_R_System R
  join dbo.v_RA_System_SystemOUName OU on OU.ResourceID = R.ResourceID
  join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = R.ResourceID
Where 
  FCM.CollectionID = 'SMS00001'
group by
  R.Name0

Last Reboot Report

Garth — Sun, 18 Mar 2012 16:38:27 GMT

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/e0147d8a-8946-42bb-b6ed-a3c92bae5b23

select  distinct
  R.ResourceID, 
  R.Netbios_Name0 AS 'Computer Name', 
  OS.LastBootUpTime0 AS 'Last Boot Time', 
  R.Resource_Domain_OR_Workgr0 AS 'Domain/Workgroup', 
  S.SiteCode as 'SMS Site Code',
  adv.time,
  Datediff(dd,OS.LastBootUpTime0, adv.time)
from 
  dbo.v_R_System_Valid R
  inner join dbo.v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID) 
  inner join dbo.v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID)
  left  join dbo.v_Site S on (FCM.SiteCode = S.SiteCode) 
  join (select MachineName as 'Name', Max(Time) as 'Time' from  dbo.v_StatusMessage Where  MessageID = 10008 Group by MachineName) Adv on R.Netbios_Name0 = Adv.Name
Where 
  FCM.CollectionID = @CollectionID 
Order by 
  R.Netbios_Name0

Lists people and how much they've been using specified software with OU fix

Garth — Fri, 16 Mar 2012 22:53:00 GMT

For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=234978&mpage=1#234980

select 
  U.Name0 as 'Bruger', 
--  U.displayName0 as 'Bruger', 
  MU.FullName as 'Brugernavn', 
  ou.ou as OU, 
  round(sum(MUS.UsageTime)/60.0,2) as 'Forbrug (min)' 
from 
  dbo.v_R_System R
  join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID 
  join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID 
  join dbo.v_MeteredUser MU on MUS.MeteredUserID = MU.MeteredUserID 
  join dbo.v_R_User U on U.Unique_User_Name0 = MU.FullName 
  left join (select OU.ResourceID, replace(max(ou.User_OU_Name0),'Gartek.LOCAL/Ottawa/','') as OU  from  v_RA_User_UserOUName OU group by  OU.ResourceID) as OU on OU.ResourceID = U.ResourceID 
where 
  MF.ProductName = @RuleName 
Group by 
  U.Name0,
  MU.FullName, 
  ou.ou
 order by  
  U.Name0

Lists people and how much they've been using specified software

Garth — Fri, 16 Mar 2012 10:40:00 GMT

For full details see: http://www.myitforum.com/forums/Need-some-SQL-help-think-its-simple-m234978.aspx

select 
  U.Name0 as 'Bruger', 
--  U.displayName0 as 'Bruger', 
  MU.FullName as 'Brugernavn', 
  replace(max(ou.User_OU_Name0),'Gartek.LOCAL/Ottawa/','') as OU, 
  round(sum(MUS.UsageTime)/60.0,2) as 'Forbrug (min)' 
from 
  dbo.v_R_System R
  join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID 
  join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID 
  join dbo.v_MeteredUser MU on MUS.MeteredUserID = MU.MeteredUserID 
  join dbo.v_R_User U on U.Unique_User_Name0 = MU.FullName 
  left join v_RA_User_UserOUName ou on ou.ResourceID = U.ResourceID 
where 
  MF.ProductName = @RuleName 
Group by 
  U.Name0,
  MU.FullName, 
  ou.ResourceID 
 order by  
  U.Name0

List collection name and number of resources by OS

Garth — Wed, 14 Mar 2012 19:49:36 GMT

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/370ceff1-0f54-46f3-8e8e-5459069125e4

SELECT 
  C.Name,
  OS.Caption0 as 'Operating System',
  OS.CSDVersion0 as 'Service Pack', 
  COUNT(*) AS 'Count'
FROM
  dbo.v_R_System R
  join dbo.v_GS_OPERATING_SYSTEM OS on OS.Resourceid = R.Resourceid 
  join dbo.v_FullCollectionMembership FCM on FCM.Resourceid = R.Resourceid 
  join dbo.v_Collection C on C.CollectionID = FCM.CollectionID
GROUP BY 
  C.Name,
  OS.Caption0,
  OS.CSDVersion0 
ORDER BY 
  C.Name,
  OS.Caption0,
  OS.CSDVersion0

78!

Garth — Wed, 14 Mar 2012 02:06:28 GMT

I have been told that we are 78 people short of the 1200 people for tomorrows Webcast! Will you be the one to help set a new record for myITForm webcast? Sign up now! http://bit.ly/yMimvp

And don’t forget that I have BIG news to spill at the end of the Webcast!

Add Serial Number and Manufacturer to “Computers that do not meet the minimum system requirements for Windows 7” report

Garth — Tue, 13 Mar 2012 23:15:33 GMT

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2ed1b090-2b2b-4a3f-9359-e1ac5fd0adba

SELECT DISTINCT 
  SYS.Netbios_Name0, 
  CS.Manufacturer0,
  BIOS.SerialNumber0,
  fcm.SiteCode, 
  ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,
  ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 AS 'Processor (GHz)', 
  ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, 
  ISNULL(CONVERT(VARCHAR(7), MAX (DA.AdapterRAM0) / 1024), 'UNKNOWN') AS AdapterRAM,
  OPSYS.Caption0 AS 'Operating System',
  CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)) Version
FROM 
  dbo.v_FullCollectionMembership fcm
  JOIN dbo.v_R_System SYS ON fcm.ResourceID=SYS.ResourceID
  JOIN dbo.v_GS_PROCESSOR PROC1 ON SYS.ResourceID = PROC1.ResourceID
  JOIN dbo.v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID
  LEFT OUTER JOIN dbo.v_GS_VIDEO_CONTROLLER DA ON SYS.ResourceID = DA.ResourceID
  JOIN dbo.v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID
  JOIN dbo.v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID AND LDISK.DeviceID0 = SUBSTRING(OPSYS.WindowsDirectory0,1,2)
  JOIN dbo.v_GS_COMPUTER_SYSTEM CS on SYS.ResourceID = CS.ResourceID
  JOIN dbo.v_GS_PC_BIOS BIOS  on SYS.ResourceID = BIOS.ResourceID
WHERE 
  ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) >= 1024
  AND ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 >= 1 
  AND ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) >= 16
  AND fcm.CollectionID = @CollID
GROUP BY 
  SYS.Netbios_Name0, 
  CS.Manufacturer0,
  BIOS.SerialNumber0,
  fcm.SiteCode, 
  LDISK.FreeSpace0, 
  PROC1.MaxClockSpeed0, 
  MEM.TotalPhysicalMemory0, 
  OPSYS.Caption0, 
  OPSYS.Version0