OWSUG.ca

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

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,
  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 
	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

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

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