SQL QUERY FOR SCCM ADMIN’S

 

You must know these collections as handy:collections End to End

1.Client all system collection

2.Non client systems

3.Inactive systems

4.Obsolete system

5.Duplicate client Yes or No

6. Last hardware inventory 14 days

7.Last software inventory 14 days

8.Last Data discovery cycle.

9. XYZ package, XYZ Advertisement success systems’ collection

10. XYZ package, XYZ Advertisement Failed systems’ collection

11.XYZ subnet collection system

12.Add XYZ system’s to a collection of Existing

13. All SMS server system collection

14. All windows server, workstation,DP,BDP collection

15. All system’s with AD site based

16. Collection limiting to sub collection, linking

17.System’s are in “A” collection But not in “B” Collection & Vice versa

18. In collection “XYZ” Software installed system

19. In collection “XYZ” File inventory(s\w inventory based) installed system

20. In collection “XYZ” file specific method(H\w inventory based) system

21.XYZ patch Installed & Not Installed system

22. All windows update Agent version 7.6 below

23.XYZ user/group collection

–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 of codes using the following sql query

 select distinct messagestate,MessageStateName from dbo.v_AdvertisementStatusInformation
ConfigMgr sccm patching status based collections

LastEnforcementMessageID LastEnforcementMessageName

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 pending, the WQL query for the collection should look like this:

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

SMS 2003 Patching :Pending for reboot collection

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”

FIND Collections and it’s Name in the console SQL Query

SELECT     Name, CollectionID FROM         dbo.v_Collection

Sample Prompt option for Collection

begin if (@__filterwildcard = ”) select v_Collection.CollectionID, v_Collection.Name from v_Collection order by v_Collection.Name else select v_Collection.CollectionID, v_Collection.Name from v_Collection WHERE v_Collection.CollectionID like @__filterwildcard order by v_Collection.Name end

Restoring Deleted All Systems Collection

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 do the restore:

####begin script

strSMSServer = “.” strParentCollID = “COLLROOT” ‘This example creates the collection in the collection root. ‘Replace COLLROOT with the CollectionID of an existing collection to make the new collection a child.

strCollectionName = “All Systems” strCollectionComment = “This is the All Systems Collection.” Set objLoc = CreateObject(“WbemScripting.SWbemLocator”) Set objSMS = objloc.ConnectServer(strSMSServer, “root\sms”) Set Results = objSMS.ExecQuery (“SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true”)

For each Loc in Results If Loc.ProviderForLocalSite = True Then   Set objSMS = objLoc.ConnectServer(Loc.Machine, “root\sms\site_” & Loc.SiteCode) End if Next

Set newCollection = objSMS.Get(“SMS_Collection”).SpawnInstance_()

‘Create new “All Systems” collection newCollection.Name = “All Systems” newCollection.OwnedByThisSite = True newCollection.Comment = strCollectionComment newCollection.CollectionID = “SMS00001″ path = newCollection.Put_

‘Set the Relationship Set newCollectionRelation = objSMS.Get(“SMS_CollectToSubCollect”).SpawnInstance_() newCollectionRelation.parentCollectionID = strParentCollID newCollectionRelation.subCollectionID = (“SMS00001″) newCollectionRelation.Put_

####end script

Once you’ve recreated the collection with the appropriate ID, then you’ll have to import the All Systems query for your membership rules.

Upgrade Configuration Manager client from SMS 2003

  • 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 all system resources with a client version not 4.00.6487.2000. Collection query is: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 SMS_R_System.ClientVersion != "4.00.6487.2000" This way, system resources with older client version will be members of this collection.

     

  • Created a package and program with ConfigMgr client upgrade with custom command line parameters. Program command line is:CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=CFM.DOMAIN.COM SMSMP=CFM.DOMAIN.COM RESETKEYINFORMATION=TRUE
  • Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

Create a collection with systems without Adobe Reader 9

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s