only Desktops in collection select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes != “10”
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) > 21 )
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System 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 = “Microsoft Systems Management Server 2003 Administrator Console”
ignore other collection select SMS_R_System.ResourceID,SMS_R_System.ResourceType, SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where Client = 1 and ClientType = 1 and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)
Some times old version could cause of some issues… below is a quick query to find the old version of systems select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ((DATEDIFF(hh, SMS_R_SYSTEM.AgentTime, getdate()) < 23) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) and ( SMS_R_System.ClientVersion is null)
All computers that are in a state of pending restart: select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from sms_r_system AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 9 All computers that failed to install an update: select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from sms_r_system inner join SMS_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid where … Read more
To create a collection based on failed advertisement systems. SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceIDWHERE AdvertisementID = ‘CEN12345′ and LastStateName = “Failed”
Collection for computers that failed to run an advertisement https://blog.coretech.dk/confmgr07/collection-for-computers-that-failed-to-run-an-advertisement/ Who is installed Software’s ? https://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/audit-software-installations/ Troubleshooting SCCM Software Updates https://www.myitforum.com/myITToolbar/frame-click.asp? https://blogs.technet.com/b/sudheesn/archive/2010/11/10/troubleshooting-sccm-part-iii-software-updates.aspx VB Script to Clear SCCM Client Cache (C:WindowsSystem32CCMCache) ——- on error resume next dim oUIResManagerdim oCachedim oCacheElementdim oCacheElements set oUIResManager = createobject(“UIResource.UIResourceMgr”) if oUIResManager is nothing then wscript.echo “Couldn’t create … Read more
Source:–https://blogs.microsoft.co.il/blogs/doli/archive/2011/04/27/sccm-copy-and-paste-context-menu-add-on.aspx SCCM : Copy and Paste, context menu add-on I like SCCM (System Center Configuration Manager) and in my opinion it is a great management tool, but it’s developers, apparently, forgot the small things that makes it better. One of the missing feathers is the copy & paste menu option (“Elementary my dear Watson”). When … Read more
SELECT DISTINCT TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code], CASE WHEN UCS.Status = ‘2’ THEN ‘Applicable’ WHEN UCS.Status = ‘3’ THEN ‘Installed’ ELSE ” END AS ‘Patch Status’, UI.BulletinID, UI.ArticleID, UI.Title, dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionIDFROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN dbo.v_R_System AS SYS LEFT … Read more
