SMSUG.ca

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

Report for matching names

Garth — Mon, 12 Mar 2012 15:53:53 GMT

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/8bda0fe9-7d80-44d0-9a52-f1f573423abf/

select 
  CS.ResourceID,
  cs.NAME0,
  PC2.name0,
  substring(CS.NAME0,5,LEN(cs.NAME0))
from 
  dbo.v_GS_COMPUTER_SYSTEM CS,
  (select CS.ResourceID, cs.NAME0, substring(CS.NAME0,5,LEN(cs.NAME0)) as 'TAG' from  dbo.v_GS_COMPUTER_SYSTEM cS) as PC2
Where
  substring(CS.NAME0,5,LEN(cs.NAME0)) = PC2.Tag
  and PC2.Tag != ''
  and PC2.ResourceID != CS.ResourceID

Total SQL Server Systems Query

Garth — Mon, 12 Mar 2012 14:19:05 GMT

See forum post for full details: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/51ead03a-a43d-46df-b0ab-059d9322e432

SELECT DISTINCT 
  R.Name0, 
  R.User_Name0, 
  ARP.DisplayName0 AS [Software Name],
  ARP.Version0 AS [Installed Version], 
  IP.IP_Addresses0 AS IPAddress,
--  ARP.TimeStamp AS [Last Time Stamp], 
  ARP.InstallDate0 AS [Software Installed Date]
 FROM
  dbo.v_R_System R
  inner join dbo.v_FullCollectionMembership FCM ON FCM.ResourceID  = R.ResourceID 
  INNER JOIN dbo.v_Add_REMOVE_PROGRAMS ARP ON R.ResourceID = ARP.ResourceID 
  INNER JOIN dbo.v_RA_System_IPAddresses IP ON R.ResourceID = IP.ResourceID
 WHERE     
  (ARP.DisplayName0 LIKE 'Microsoft SQL Server 2008')
  or (ARP.DisplayName0 LIKE 'Microsoft SQL Server 2005')
  or (ARP.DisplayName0 LIKE 'Microsoft SQL Server 2000')
  or (ARP.DisplayName0 LIKE 'Microsoft SQL Server 2008 R2 (64-bit)') 
  AND (FCM.CollectionID IN ('SMS00001'))

Link v_MeteredUser.FullName to a full name from AD v2

Garth — Mon, 12 Mar 2012 14:10:28 GMT

To see full post see http://www.myitforum.com/forums/tm.aspx?high=&m=234899&mpage=1#234905

select 
  R.Netbios_Name0 as 'Computernavn', 
--  U.displayName0 as 'Bruger', 
  Full_User_Name0,
  max(ou.User_OU_Name0),
  MU.FullName as 'Brugernavn', 
  MUS.LastUsage as 'Sidst brugt', 
  MUS.UsageCount + MUS.TSUsageCount  as 'Antal brug', 
  round(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 
  join dbo.v_RA_User_UserOUName ou on ou.ResourceID = u.ResourceID
 where 
  MF.ProductName = @RuleName 
group by 
  R.Netbios_Name0, 
--  U.displayName0, 
  Full_User_Name0,
  MU.FullName, 
  MUS.LastUsage, 
  MUS.UsageCount + MUS.TSUsageCount , 
  round(MUS.UsageTime/60.0,2)
order by 
  R.Netbios_Name0

Link v_MeteredUser.FullName to a full name from ad

Garth — Mon, 12 Mar 2012 12:34:39 GMT

To see the full post http://www.myitforum.com/forums/How-to-link-v_MeteredUserFullName-to-a-full-name-from-ad-m234899.aspx

select 
  R.Netbios_Name0 as 'Computernavn', 
--  U.displayName0 as 'Bruger', 
  Full_User_Name0,
  MU.FullName as 'Brugernavn', 
  MUS.LastUsage as 'Sidst brugt', 
  MUS.UsageCount + MUS.TSUsageCount  as 'Antal brug', 
  round(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 
 where 
  MF.ProductName = @RuleName 
 order by 
  R.Netbios_Name0

BIG NEWS!!!!

Garth — Mon, 12 Mar 2012 02:40:34 GMT

I have big news but there is a catch….. I can’t say much until Wednesday at noon PST! J Which works out perfectly, as that is almost the exact time that I will be finishing my SCCM Guru webcast! So make sure that your register for the session to hear it Live! http://bit.ly/yMimvp

oh, you want a hint?!?! Just a little hint??? Let’s just say that it involves MMS!

Link to Name

Garth — Sun, 11 Mar 2012 15:32:18 GMT

For full details see post here. http://www.myitforum.com/forums/tm.aspx?high=&m=234875&mpage=1#234894

select 
  ARP.resourceid, 
  CS.Name0,
  max(ARP.version0)as JavaVersion 
from 
  dbo.v_GS_COMPUTER_SYSTEM as CS
  join dbo.v_ADD_REMOVE_PROGRAMS as ARP on CS.ResourceID = ARP.ResourceID
where 
  ARP.DisplayName0 like 'Java(TM) 6 Update%' 
group by 
  ARP.resourceid,
  CS.Name0

Cannot get rid of duplicate rows on an Add or Remove Programs query

Garth — Sun, 04 Mar 2012 21:52:23 GMT

For for details see post. http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/3bea8fa6-968a-4f0e-b412-469577e409aa

Select DISTINCT 
  SYS.Netbios_Name0 AS 'Name',
  case SEM.ResourceID
    When NULL THEN 'N'
    Else 'Y'
  End AS 'SafeNet?',
  ARP.Timestamp
  
From 
  dbo.v_R_System SYS
  left outer JOIN dbo.v_Add_Remove_Programs ARP ON SYS.ResourceID  = ARP.ResourceID and ARP.DisplayName0 = 'Update for Windows Server 2003 (KB943295)'
  LEFT OUTER JOIN (SELECT ARP.ResourceID From dbo.v_Add_Remove_Programs ARP WHERE ARP.DisplayName0 = 'Update for Windows Server 2003 (KB943295)') SEM on SYS.ResourceID = SEM.ResourceID 
WHERE 
  SYS.Netbios_Name0 LIKE 'Dell%' 
ORDER BY 
  SYS.Netbios_Name0

Custom ChassisType Report - wrong counts

Garth — Sun, 04 Mar 2012 17:30:02 GMT

See the full details within the forum http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/d8477233-4b10-4a6d-a5ea-de73abc9b815/

declare @Manufacturer0 as varchar(250)
set @Manufacturer0 = 'All'
declare @Version0 as varchar(250)
set @Version0 = 'All'
declare @CollectionID as varchar(250)
set @CollectionID = 'SMS00001'
SELECT 
  SE.ChassisTypes0,
  count (*) as ChassisCount 
FROM
  dbo.v_GS_SYSTEM_ENCLOSURE SE
  INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SE.ResourceID
  INNER JOIN dbo.v_FullCollectionMembership FCM ON FCM.ResourceID = SE.ResourceID
WHERE
  (SE.Manufacturer0 = @Manufacturer0 or @Manufacturer0 = 'All')
  AND (OS.Version0 = @Version0 or @Version0 = 'All')
  AND (FCM.CollectionID = @CollectionID or @CollectionID = 'All')
GROUP BY 
  SE.ChassisTypes0

Software Metering Test query

Garth — Fri, 02 Mar 2012 19:39:06 GMT

select 
  CS.Name0, 
  MRIB.ProductName,
  MD.* 
from
  dbo.v_GS_COMPUTER_SYSTEM CS
  join dbo.v_MeterData MD on CS.ResourceID = MD.ResourceID
  join dbo.v_MeterRuleInstallBase MRIB on MD.FileID = MRIB.MeteredFileID
where 
  MD.starttime > '2012-02-16'

Tips and Tricks for SSRS

Garth — Fri, 24 Feb 2012 22:27:47 GMT

I want to let everyone know that I will be giving a webcast on SSRS Report Writing for CM07/CM12 on Wednesday, March 14, 2012, at 11:00 a.m. to 12:30 pm PST.

Episode 9: Garth Jones

A Deep Dive into SCCM Data! Bring Your Snorkel!

In this session, I will dive deep into the depths of ConfigMgr, and show you tricks that can be used for both SCCM 2007 and SCCM 2012! When you swim with the sharks, the tips provided in this session will help you from being eaten alive. You will learn items such as how to create a template for SSRS reports that any manager will want!

For more details and to register, see: http://bit.ly/yMimvp

ONE Row for each Server

Garth — Fri, 24 Feb 2012 02:12:17 GMT

For full details see the forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/bbc5e667-085f-47a2-a5b6-8a5089aa9e91

SELECT Distinct 
  R.Name0 as 'PC', 
  LD.FreeSpace0 as 'Free Space', 
  LD.Size0 as 'Size',
  isnull(Share.Path0,'n/a') as 'Path'
FROM 
  dbo.v_R_System R
  inner join dbo.v_GS_SERVICE Serv on R.ResourceID = Serv.ResourceID
  inner join dbo.v_GS_LOGICAL_DISK LD on  LD.ResourceID = R.ResourceID
  left outer join (select ResourceID, Name0, Path0 from v_GS_SHARE Where Name0 = 'Print$') Share on  Share.ResourceID = R.ResourceID 
 Where 
  Serv.DisplayName0 LIKE '%Windows Deployment Services%'
  AND LD.Name0 = 'D:'
 ORDER BY 
  R.Name0

Display the User Group Name in 1 column

Garth — Fri, 17 Feb 2012 13:43:53 GMT

To see full forum post see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/986960ba-8a50-4e69-9e5a-64a40c9d8df5/

SELECT 
  R.User_Name0 as 'user name', 
  U.Full_User_Name0 as 'full name', 
  U.givenName0 as 'First Name', 
  U.sn0 as 'Surname', 
  U.mail0 as 'Email',  
  max(OU.User_OU_Name0) as 'OU Path',
  Groups.Groups as 'User Group'
 FROM  
  dbo.v_R_System R
  JOIN dbo.v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID
  JOIN dbo.v_R_User U ON R.User_Domain0+ '\'+R.User_Name0 = U.Unique_User_Name0
  left outer join dbo.v_RA_User_UserOUName OU on U.ResourceID = OU.ResourceID
  join (SELECT U.ResourceID, STUFF((  SELECT 
                      ',' + UG.User_Group_Name0 
                    FROM 
                      dbo.v_RA_User_UserGroupName UG 
                    WHERE 
                      U.ResourceID = UG.ResourceID 
                    FOR XML PATH('')),1,1,'') AS Groups 
                    FROM 
                      dbo.v_R_User U) as Groups on U.ResourceID = Groups.ResourceID
WHERE 
  OU.User_OU_Name0 Like '%SQL%'
Group By 
  R.User_Name0, 
  U.Full_User_Name0, 
  U.givenName0, 
  U.sn0, 
  U.mail0,
  Groups.Groups
order By 
  R.User_Name0