January 2012 - Posts

SCCM Query for Memory & CPU
Tuesday, January 31, 2012 9:18 PM

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

by Garth Jones

Filed under: ConfigMGr, SMS, Code, WQL, SCCM, CM12

Custom Windows 7 report - workstation manufacturer & model & client version
Monday, January 16, 2012 8:21 PM

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

by Garth Jones

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

Custom Windows 7 report - workstation manufacturer & model type
Monday, January 16, 2012 6:03 PM

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

by Garth Jones

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

WQL Query for both x86 and x64 version of Software
Monday, January 16, 2012 5:27 PM

 

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")

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, WQL, SCCM

Adding IP address to Client Summary data
Friday, January 13, 2012 8:49 AM

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

by Garth Jones

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

WINS/DNS for a Collection
Friday, January 13, 2012 8:41 AM

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

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL

MyITforum donation for 2012
Monday, January 02, 2012 10:21 AM

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.

by Garth Jones

Filed under: ConfigMGr, MOM, SMS, OpsMgr, System Center, SCMDM, SCSM, SCE, SCVMM, MDT, myITforum, SCCM, SMSI, SMS Installer, vNext