–Systems Part of What Collections SELECT v_R_System.Name0, v_Collection.Name FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID WHERE (v_R_System.Name0 = ‘Systemname’)
For software distribution status if system pending for reboot 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 join SMS_StatMsg as st on sms_r_system.Name = st.MachineName join SMS_AdvertisementStatusInformation sti on st.MessageID = sti.MessageID where sti.messagestate = 102 102 is the reboot pending code you can get the complete list … Read more
ConfigMgr sccm patching status based collections LastEnforcementMessageIDLastEnforcementMessageName 1 Enforcement started 3 Waiting for another installation to complete 6 General failure 8 Installing update 9 Pending system restart 10 Successfully installed update 11 Failed to install update 12 Downloading update 13 Downloaded update So in this example we would like to use the status of reboot … Read more
A collection listing all servers/clients that were pending reboot (see query statement): 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_PatchStatus on SMS_G_System_PatchStatus.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PatchStatus.LastStateName = “Reboot Pending”
SELECT Name, CollectionIDFROM dbo.v_Collection
beginif (@__filterwildcard = ”)select v_Collection.CollectionID, v_Collection.Name from v_Collection order by v_Collection.Nameelseselect v_Collection.CollectionID, v_Collection.Name from v_CollectionWHERE v_Collection.CollectionID like @__filterwildcardorder by v_Collection.Nameend
To update the collection membership 1. Log on as administrator with a password of password. 2. On the Start menu, click SMS Administrator Console. The SMS Administrator Console window appears. 3. In the console tree, expand Site Database, expand Collections, and then click All Systems. The members of the All Systems collection appear in the … Read more
Sometimes things just happen, sometimes you accidentally delete the All Systems collection because you were trying to do too many things at once. I’ll fess up, I did it. Here’s how to restore the collection with the appropriate ID. This solution was given to me by Microsoft Support. Here is the VBS script that will … Read more
Create a report that counts all client versions. (This is optional, just for information purposes). Report query is: SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1) ORDER BY Total DESC, [ConfigMgr client version] Create a collection (“Older Clients” for example) with … Read more
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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion not like “%Server%” and SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.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 like “%Adobe Reader 9%”)
