Garth Jones

PC not logon for 14 days.

Garth — Tue, 07 Feb 2012 02:04:01 GMT

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

Symantec av software to a query

Garth — Fri, 03 Feb 2012 18:47:15 GMT

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

IT Virtualization Boot Camp

Garth — Fri, 03 Feb 2012 14:26:46 GMT

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

SCCM Query for Memory & CPU

Garth — Wed, 01 Feb 2012 02:18:43 GMT

See Forum post for fill details.

select distinct 
  SMS_R_System.Name, 
  SMS_R_System.Client, 
  SMS_R_System.LastLogonUserName, 
  SMS_R_System.ADSiteName, 
  SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 
  SMS_G_System_PROCESSOR.NormSpeed 
from  
  SMS_R_System 
  inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId 
  left join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.ResourceID = SMS_R_System.ResourceId 
  inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where 
  SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName is like "Adobe Reader X%"
  and SMS_R_System.Client = 1

Custom Windows 7 report - workstation manufacturer & model & client version

Garth — Tue, 17 Jan 2012 01:21:40 GMT

SELECT DISTINCT 
  COMSYS.Manufacturer0,
  COMSYS.Model0,
  Client.Version0,
  Count(*)
FROM
  v_GS_COMPUTER_SYSTEM COMSYS 
  JOIN v_FullCollectionMembership fcm ON COMSYS.ResourceID = FCM.ResourceID 
  JOIN v_GS_PROCESSOR PROC1 ON COMSYS.ResourceID = PROC1.ResourceID
  JOIN v_GS_X86_PC_MEMORY MEM ON COMSYS.ResourceID = MEM.ResourceID
  JOIN v_GS_OPERATING_SYSTEM OPSYS ON COMSYS.ResourceID = OPSYS.ResourceID
  JOIN v_GS_LOGICAL_DISK LDISK ON COMSYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
  JOIN v_GS_Client0 CLIENT ON COMSYS.ResourceID = CLIENT.ResourceID
 WHERE 
  (fcm.CollectionID='SMS00001')
  AND (ROUND (ROUND(CONVERT (FLOAT ,mem.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
  OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1 
  OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)
Group by 
  COMSYS.Manufacturer0,
  COMSYS.Model0,
  Client.Version0

Custom Windows 7 report - workstation manufacturer & model type

Garth — Mon, 16 Jan 2012 23:03:44 GMT

SELECT DISTINCT 
  COMSYS.Manufacturer0,
  COMSYS.Model0,
  Count(*)
FROM
  v_GS_COMPUTER_SYSTEM COMSYS 
  JOIN v_FullCollectionMembership fcm ON COMSYS.ResourceID = FCM.ResourceID 
  JOIN v_GS_PROCESSOR PROC1 ON COMSYS.ResourceID = PROC1.ResourceID
  JOIN v_GS_X86_PC_MEMORY MEM ON COMSYS.ResourceID = MEM.ResourceID
  JOIN v_GS_OPERATING_SYSTEM OPSYS ON COMSYS.ResourceID = OPSYS.ResourceID
  JOIN v_GS_LOGICAL_DISK LDISK ON COMSYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
 WHERE 
  (fcm.CollectionID='SMS00001')
  AND (ROUND (ROUND(CONVERT (FLOAT ,mem.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
  OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1 
  OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)
Group by 
  COMSYS.Manufacturer0,
  COMSYS.Model0

WQL Query for both x86 and x64 version of Software

Garth — Mon, 16 Jan 2012 22:27:42 GMT

select distinct 
  SMS_R_System.Name,
  SMS_R_System.ResourceId
from  
  SMS_R_System 
where 
  SMS_R_System.ResourceId in (  select 
            SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID 
          from 
            SMS_G_System_ADD_REMOVE_PROGRAMS 
          where 
            SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Lync 2010")
  or SMS_R_System.ResourceId in (  select 
            SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID 
          from 
            SMS_G_System_ADD_REMOVE_PROGRAMS_64 
          where 
            SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Lync 2010")

Adding IP address to Client Summary data

Garth — Fri, 13 Jan 2012 13:49:13 GMT

select 
	NetBiosName as "CAM NetBiosName", 
	LastPolicyRequest as "CAM LastPolicyRequest", 
	LastDDR as "CAM LastDDR", 
	sy.Operating_System_Name_and0 as "Operating System", 
	sy.Resource_Domain_OR_Workgr0 as "AD Domain",
	NAC.IPAddress0
from 
	v_CH_ClientSummary
	inner join v_R_System as sy on sy.ResourceID=MachineID 
	join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on sy.ResourceID = NAC.ResourceID
where 
	sy.Operating_System_Name_and0 like '%workstation%'
	and NAC.IPaddress0 is Not Null

WINS/DNS for a Collection

Garth — Fri, 13 Jan 2012 13:41:44 GMT

SELECT  
	S.Name0 AS [System Name],
	OS.Caption0 AS [Operating System],
	OS.CSDVersion0 AS [Service Pack],
	OS.InstallDate0 AS [Original OS Install Date],
	OS.LastBootUpTime0 AS [Last Boot Time],
	NAC.IPAddress0 AS [IP Address],
	NAC.IPSubnet0 AS [Subnet Mask],  
	NAC.DefaultIPGateway0 AS [Gateway],
	NAC.DNSServerSearchOrder0 AS [DNS Servers],
	NAC.WINSPrimaryServer0 AS [Pri WINS],
	NAC.WINSSecondaryServer0 AS [Sec WINS],
	BIOS.SMBIOSBIOSVersion0 AS [System ROM Version], BIOS.ReleaseDate0 AS [System ROM Date]
 FROM
	dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC 
	INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON NAC.ResourceID = OS.ResourceID 
	INNER JOIN dbo.v_GS_PC_BIOS BIOS ON OS.ResourceID = BIOS.ResourceID 
	INNER JOIN dbo.v_GS_SYSTEM S ON BIOS.ResourceID = S.ResourceID
	join dbo.v_FullCollectionMembership_Valid FCM on NAC.ResourceID = FCM.ResourceID 
 where  
	FCM.CollectionID = @variable and 
	NAC.IPaddress0 is Not Null

MyITforum donation for 2012

Garth — Mon, 02 Jan 2012 15:21:58 GMT

With the start of a new year, it is that time again to support the System Center Community, with that in mind I have made my yearly donation of $250 USD to MyITForum (MIF).

http://myitforum.com/myitforumwp/aboutus/donations/

MIF is truly a community that helps each other both professionally and personally. But it cost thousands of dollars each MONTH to keep this community going. MIF has worked hard to get sponsor to defray the cost however we as the community should do our part. I encourage you to donate to MIF, no you don’t need to match my donation but you surely can afford to donate the equivalent of 1 Beer ($10) to a community that you use every day.

64 Bit Office

Garth — Thu, 29 Dec 2011 22:04:26 GMT

SELECT

CS.Name0,

ARP64.DisplayName0,

ARP64.Version0

FROM

dbo.v_GS_COMPUTER_SYSTEM CS

join dbo.v_GS_ADD_REMOVE_PROGRAMS_64 ARP64 on CS.ResourceID = ARP64.ResourceID

Where

ARP64.DisplayName0 like '%office%'

In response to http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/498ffe87-9a3d-433f-9927-020fd4c3777b

Report Two items from Add/Remove Program

Garth — Wed, 21 Dec 2011 00:00:20 GMT

Select 
	Name0
From
	dbo.v_GS_COMPUTER_SYSTEM CS
	join dbo.v_FullCollectionMembership FCM on CS.ResourceID = FCM.ResourceID
Where 
	CS.ResourceId in (select ARP.ResourceId from dbo.v_Add_Remove_Programs ARP Where ARP.Displayname0 = 'Windows Server 2003 Service Pack 2')
	and CS.ResourceId in (select ARP.ResourceId from dbo.v_Add_Remove_Programs ARP Where ARP.Displayname0 = 'McAfee VirusScan Enterprise')
	and FCM.CollectionID = 'SMS00001'
http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2ebeb8f8-13be-4934-8123-c1c9e8003b87

Challenge aka 70-659

Garth — Mon, 28 Nov 2011 15:15:21 GMT

On Thursday night at the User Group event, I was challenged to write 70-659 exam before Dec 31 2011. There are high stakes at risk here, bragging rights and more importantly beer! Over the next few weeks you will see a few blogs post from me on the subject as a way for me to study for the exam.

I spent a bit of time on the weekend, starting to get my act in order!

First things first, study material!

I have a reasonable lab setup but if you don’t you will need to get the software. The easy way to do that is download it from the Eval Center.

My next move was to sign up for the MS Virtual Academy, there are a number of session there like that will help out:

· Microsoft Virtualization for VMware Professionals – Management

· Microsoft Virtualization for VMware Professionals – VDI

· Enhancing Your Business and Career with the Private Cloud

· Planning, Building and Managing a Private Cloud

· System Center Virtual Machine Manager 2012

FYI currently my ranking is 266152, I think that I can change that in a few days. J

Don’t forget about TechNet Cloud Hub.

Luck for me, MS Canada TechNet team sent out a link to a free eBook for 70-659, you can download yourself and you don’t need to be a Canadian to get it either! J Don’t tell any of the MS guys but I have loaded it on my iPad already. J

Finally, I printed off the skills being measured for this exam. http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-659#tab2

Are you up to the Challenge? Did you think that you can write this exam by Dec 31, 2011?

Query on Prohibited application for specific OU

Garth — Mon, 28 Nov 2011 13:39:22 GMT

Select distinct 
	ou.system_ou_name0,
	sys.Netbios_Name0, 
	fcm.SiteCode,sys.User_Domain0, 
	sys.User_Name0,
--	DATEADD(mi,(usr.lastLogontimeStamp0  / 600000000) - 157258080,0) AS [Last Logon Time Stamp (UTC)],
	usr.company0, 
	sys.Operating_System_Name_and0, 
	Displayname0,
	Version0,
	InstallDate0
FROM 
	dbo.v_R_System sys 
	JOIN dbo.v_r_User usr on SYS.user_name0 = usr.user_name0 
	join dbo.v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID 
	JOIN dbo.v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID 
	left outer join dbo.v_RA_System_SystemOUName OU on sys.ResourceID = OU.ResourceID 
WHERE 
	fcm.CollectionID=@CollID	
	and ou.system_ou_name0 = 'enhansoft.com/WORKSTATIONS' 
	and (lower(DisplayName0) like '%µTorrent%' 
	or lower(DisplayName0) like '%skype%' 
	or lower(DisplayName0) like '%Remote PC%'
	or lower(DisplayName0) like '%AOL%'
	or lower(DisplayName0) like '%Ares%'
	or lower(DisplayName0) like '%Galaxy%')

Show list of computers whithout more than 30 criticals patches not installed

Garth — Sun, 27 Nov 2011 16:59:32 GMT

select    
	v_R_System.Name0 as 'Computername',    
	Count(v_StateNames.Statename) as 'Required Updates'    
from    
	v_StateNames,    
	v_Update_ComplianceStatusAll    
	Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
	Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
where    
	v_StateNames.TopicType = 500 and    
	v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and    
	v_StateNames.Statename = 'Update is required'    
Group By 
	v_R_System.Name0
Having 
	count(*) > @Var