Browse by Tags

PC not logon for 14 days.
Monday, February 06, 2012 9:04 PM

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... Read More...

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, Code, SQL, SCCM, CM12, CM07

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 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... Read More...

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 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... Read More...

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL, 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... Read More...

by Garth Jones

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

Report Two items from Add/Remove Program
Tuesday, December 20, 2011 7:00 PM

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... Read More...

by Garth Jones

Filed under: SMS, System Center, Code, SQL, SCCM

Query on Prohibited application for specific OU
Monday, November 28, 2011 8:39 AM

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... Read More...

by Garth Jones

Filed under: ConfigMGr, SQL, SCCM, vNext

Show list of computers whithout more than 30 criticals patches not installed
Sunday, November 27, 2011 11:59 AM

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... Read More...

by Garth Jones

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

Query to find multiple Net bois names on single IP
Thursday, November 24, 2011 8:51 AM

http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/c23b6c8f-57a7-4bdf-932e-e28060d4323d select CS.Name0, NAC.IPAddress0 from dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceID = NAC.ResourceID Where NAC.IPAddress0 != '' Group by CS.Name0, NAC.IPAddress0 Having count (NAC.IPAddress0) > 1 .csharpcode... Read More...

by Garth Jones

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

Simple History query
Saturday, October 22, 2011 9:49 AM

Select distinct SD.Name0 'Machine Name' , HARP.DisplayName0 as '32bit' , HARP.Version0, HARP64.DisplayName0 as '64bit' , HARP64.Version0, SD.User_Name0 'User Name' From v_R_System SD Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID Join v_Collection COL on FCM.CollectionID = COL.CollectionID left outer join... Read More...

by Garth Jones

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

Two Software Titles
Wednesday, October 19, 2011 10:47 PM

SELECT DISTINCT a.Netbios_Name0 AS 'Computer Name' , CASE WHEN ARP.OMSA IS NULL THEN 'N/A' ELSE ARP.OMSA END AS 'Dell OMSA Version' , CASE WHEN ARP.Equallogic IS NULL THEN 'N/A' ELSE ARP.Equallogic END AS 'Broadcom Version' FROM v_R_System_Valid a INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = a.ResourceID INNER... Read More...

by Garth Jones

Filed under: SMS, System Center, Code, SQL, SCCM

Advertisements, Programs and Main User of all PC
Saturday, October 01, 2011 10:16 AM

SELECT A . AdvertisementName , A . ProgramName , R . Netbios_Name0 as Computername , SCUM . TopConsoleUser0 as MainUsername FROM dbo . v_R_System_Valid R join dbo . v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on R . ResourceID = SCUM . ResourceID join dbo . v_ClientAdvertisementStatus CAS on R . ResourceID = CAS . ResourceID join dbo . v_Advertisement A on A . AdvertisementID... Read More...

by Garth Jones

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

Serial Number, User Information and installation dates of the software
Wednesday, September 28, 2011 6:28 PM

SELECT adv . AdvertisementName , stat . LastStateName , adv . Comment AS C072 , pkg . Name AS C062 , adv . ProgramName AS C071 , adv . SourceSite , adv . AdvertisementID , se . SerialNumber0 , cs . UserName0 , Stat . LastAcceptanceStatusTime FROM v_Advertisement adv JOIN v_Package pkg ON adv . PackageID = pkg . PackageID JOIN v_ClientAdvertisementStatus stat... Read More...

by Garth Jones

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

Query to fetch the machines from a specific OU with the Specific Machine Model
Tuesday, September 13, 2011 7:41 AM

Select R . Name0 From dbo . v_R_System R join dbo . v_GS_COMPUTER_SYSTEM CS on R . ResourceID = CS . ResourceID join dbo . v_RA_System_SystemOUName OU on R . ResourceID = OU . ResourceID Where CS . Model0 = 'Virtual Machine' Group by R . Name0 , CS . Model0 Having max ( OU . System_OU_Name0 ) = 'GARTEK.TST/WORKSTATIONS' Read More...

by Garth Jones

Filed under: ConfigMGr, System Center, Code, SQL, SCCM

PC Var data
Sunday, August 28, 2011 4:38 PM

select Name , Value , Masked from MEP_MachineVariables where MachineID = 63 Read More...

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, Code, SQL, SCCM, vNext

RAM by User name
Saturday, August 06, 2011 2:00 PM

SELECT Distinct SYS.Netbios_Name0 , SYS.Operating_System_Name_and0 , MEM . TotalPhysicalMemory0 / 1024 As C083 FROM v_R_System SYS JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM . ResourceID WHERE User_Name0 = 'Garth' Read More...

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, Code, SQL, SCCM, vNext

More Posts Next page »