Garth Jones at myITforum.com

Find Programs Removed from a PC (updated)

2012-05-21T14:15:59Z

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...(read more)

How to exclude a collection from sccm collection query

2012-05-19T14:47:37Z

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 "...(read more)

70-243

2012-05-16T16:51:00Z

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.. ;-) ...(read more)

LOL OOO message

2012-05-15T23:22:31Z

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 ) ...(read more)

Adobe Dashboard

2012-05-13T15:06:41Z

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)...(read more)

List of PC with Project and Subnet

2012-05-03T12:04:53Z

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...(read more)

MMS 2012

2012-04-30T13:01:29Z

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...(read more)

Report Machine's Platform information

2012-04-05T17:53:08Z

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...(read more)

Query to report OU information for Machines

2012-04-05T17:49:14Z

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...(read more)

Last Reboot Report

2012-03-18T16:38:27Z

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...(read more)

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

2012-03-16T22:53:20Z

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...(read more)

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

2012-03-16T10:40:08Z

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),' ITCSYD.LOCAL/BRUGERE/ ',' ') 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...(read more)

List collection name and number of resources by OS

2012-03-14T19:49:36Z

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...(read more)

78!

2012-03-14T02:06:28Z

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!...(read more)

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

2012-03-13T23:15:33Z

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 , PROC 1.MaxClockSpeed0), -2)/ 1000 AS ' Processor (GHz) ', ROUND (ROUND( CONVERT ( FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, ISNULL( CONVERT ( VARCHAR...(read more)