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

SCCM Query for Memory & CPU
Tuesday, January 31, 2012 9:18 PM

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

by Garth Jones

Filed under: ConfigMGr, SMS, Code, WQL, SCCM, CM12

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

WQL Query for both x86 and x64 version of Software
Monday, January 16, 2012 5:27 PM

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

by Garth Jones

Filed under: ConfigMGr, SMS, System Center, WQL, 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

MyITforum donation for 2012
Monday, January 02, 2012 10:21 AM

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

by Garth Jones

Filed under: ConfigMGr, MOM, SMS, OpsMgr, System Center, SCMDM, SCSM, SCE, SCVMM, MDT, myITforum, SCCM, SMSI, SMS Installer, vNext

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

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

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

More Posts Next page »