Month – January 2012

SCCM Query for Memory & CPU

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

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′) […]

Custom Windows 7 report – workstation manufacturer & model type

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) […]

WQL Query for both x86 and x64 version of Software

  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

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

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 […]

MyITforum donation for 2012

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 […]