Garth Jones at myITforum.com

PC not logon for 14 days.

2012-02-07T02:04:01Z

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...(read more)

Symantec av software to a query

2012-02-03T18:47:15Z

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...(read more)

IT Virtualization Boot Camp

2012-02-03T14:26:46Z

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...(read more)

SCCM Query for Memory & CPU

2012-02-01T02:18:43Z

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...(read more)

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

2012-01-17T01:21:40Z

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 PROC 1 ON COMSYS.ResourceID = PROC 1.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...(read more)

Custom Windows 7 report - workstation manufacturer & model type

2012-01-16T23:03:44Z

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 PROC 1 ON COMSYS.ResourceID = PROC 1.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...(read more)

WQL Query for both x86 and x64 version of Software

2012-01-16T22:27:42Z

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...(read more)

Adding IP address to Client Summary data

2012-01-13T13:49:13Z

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...(read more)

WINS/DNS for a Collection

2012-01-13T13:41:44Z

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...(read more)

MyITforum donation for 2012

2012-01-02T15:21:58Z

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...(read more)

64 Bit Office

2011-12-29T22:04:26Z

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...(read more)

Report Two items from Add/Remove Program

2011-12-21T00:00:20Z

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...(read more)

Challenge aka 70-659

2011-11-28T15:15:21Z

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...(read more)

Query on Prohibited application for specific OU

2011-11-28T13:39:22Z

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...(read more)

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

2011-11-27T16:59:32Z

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...(read more)