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

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

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

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

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

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

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

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

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

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.

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

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

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.

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%') 

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