February 2012 - Posts

Tips and Tricks for SSRS
Friday, February 24, 2012 5:27 PM

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

by Garth Jones

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

ONE Row for each Server
Thursday, February 23, 2012 9:12 PM

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 

by Garth Jones

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

Display the User Group Name in 1 column
Friday, February 17, 2012 8:43 AM

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

by Garth Jones

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

AD discovery and add the following attributes (givenname, sn, mail & ou) report
Tuesday, February 14, 2012 9:08 PM

For full details see. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7f48aa48-d260-4ad1-babc-8b2496d103fb

I fix the query to make it work, change OS to a readable name, formatting etc..

SELECT 
  R.Name0 as 'Host Name', 
  CS.Model0 as 'model', 
  BIOS.SerialNumber0 as 'SN', 
  OS.Caption0 as 'OS', 
  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'
 FROM 
  dbo.v_R_System R
  JOIN dbo.v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID
  JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID
  JOIN dbo.v_GS_PC_BIOS BIOS ON R.ResourceID = BIOS.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 dbo.v_GS_OPERATING_SYSTEM os on CS.ResourceID = OS.ResourceID
 WHERE 
  FCM.CollectionID = @CollectionID
Group By 
  R.Name0,
  CS.Model0, 
  BIOS.SerialNumber0, 
  OS.Caption0,
  R.User_Name0, 
  U.Full_User_Name0, 
  U.givenName0, 
  U.sn0, 
  U.mail0  

by Garth Jones

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

PC not logon for 14 days.
Monday, February 06, 2012 9:04 PM

For full details see. http://social.technet.microsoft.com/Forums/en-US/configmgrsdk/thread/cf7d80a0-1a7c-430c-b33a-ee47a0efa806

 

select 
  CS.Name0,
  LCU.date
From 
  dbo.v_GS_COMPUTER_SYSTEM CS
  join (  Select 
        ResourceID, 
        max(LastConsoleUse0) as 'date' 
      from 
        dbo.v_GS_SYSTEM_CONSOLE_USER SCU 
      Group by 
        ResourceID) as LCU on CS.ResourceID = LCU.ResourceID
Where
  datediff(dd,LCU.date,getdate()) >14

by Garth Jones

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

Symantec av software to a query
Friday, February 03, 2012 1:47 PM

Adding symantec av software to a query, see here for full details.http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7c6b65be-cdfc-414f-a78d-f198f5e6a323

 

SELECT 
  Sys.Netbios_Name0 as 'Computer Name', 
  Sys.User_Domain0 as 'User Domain', 
  Sys.User_Name0 as 'UserName', 
  Comp.Manufacturer0 as 'Make', 
  Comp.Model0 as 'Model', 
  Process.Name0 as 'Processor', 
  BIOS.SerialNumber0 as 'Serial#', 
  Memory.TotalPhysicalMemory0/1000 as 'RAM (MB)', 
  Sys.Operating_System_Name_and0 as 'Operating System', 
  OS.CSDVersion0 as 'Service Pack', 
  Status.LastHWScan as 'Last H/W Scan', 
  OS.InstallDate0 as 'Install_Date', 
  OS.LastBootUpTime0 as 'Last Bootup', 
  Sys.AD_SITE_NAME0 as 'Location',
  SSAS.SMS_Assigned_Sites0,
  ie.FileVersion0 
 
FROM v_R_System Sys
  LEFT JOIN v_GS_COMPUTER_SYSTEM Comp on Sys.ResourceID = Comp.ResourceID
  LEFT JOIN v_GS_PC_BIOS BIOS on Sys.ResourceID = BIOS.ResourceID
  LEFT JOIN v_GS_X86_PC_MEMORY Memory on Sys.ResourceID = Memory.ResourceID
  LEFT JOIN v_GS_PROCESSOR Process on Sys.ResourceID = Process.ResourceID
  LEFT JOIN v_GS_WORKSTATION_STATUS Status on Sys.ResourceID = Status.ResourceID
  LEFT JOIN v_GS_OPERATING_SYSTEM OS on Sys.ResourceID = OS.ResourceID
  INNER JOIN dbo.v_RA_System_SMSAssignedSites SSAS ON SYS.ResourceID = SSAS.ResourceID
  LEFT JOIN dbo.v_GS_INSTALLED_EXECUTABLE IE on SYS.ResourceID = IE.ResourceID and IE.ExecutableName0 = 'PWConsole.exe'  
WHERE 
  OS.LastBootUpTime0 <> ' ' 
--  and (Sys.Name0 like '0___0-TC-%' or Sys.Name0 like '63___-TC-%')
ORDER BY 
  Sys.Netbios_Name0

by Garth Jones

IT Virtualization Boot Camp
Friday, February 03, 2012 9:26 AM

Did you know that the OWSUG is putting on the IT Virtualization Boot Camp? This boot camp is designed to help you pass the 70-659 exam…

 

What do you need to know. the boot camp cost ~$27, there are two date for this boot camp Feb 17 & 18.  Spots are going fast soo, book now!

 

http://www.eventbrite.ca/event/2892520607

by Garth Jones

Filed under: Virtual Server , Windows 2008, self-study, exam, Hyper-v, 70-659