SMS SQL for Duplicate Collection Names

Recently, I've found many collections with duplicate names. The dupes can cause problems when managing those collections. I quickly created the following SQL to find the offending collections.

-- This SQL query will show collections with duplicate names.

Select parentCollectionID, ParentCollection.Name, v_Collection.CollectionID, v_Collection.Name from v_Collection
left outer join
v_CollectToSubCollect
on v_Collection.collectionid=v_CollectToSubCollect.subcollectionid
left outer join
v_Collection ParentCollection
on v_CollectToSubCollect.parentCollectionID = ParentCollection.collectionid
where v_Collection.Name in (Select Name from v_Collection Group By Name Having Count(*)>1)

About the Author

Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin."

Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services.

Visit EmmanuelTsouris.com for his latest projects.

As an Amazon Associate, I earn from qualifying purchases at no cost to you.

Comments

Post a Comment

Popular posts from this blog

SMS "Program failed (run time exceeded)" 10070

When a program fails due to error 10070, it does so because the Run Time was Exceeded. SMS Allows a program to run for 12 hours, afterwhich it will issue the failed status message if the program hasn't completed successfully. Note: The Maximum allowed run time within program properties is generally a guide for users running the program themselves, and is not enforced. About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . Visit EmmanuelTsouris.com for his latest projects. As an Amazon Associate, I earn from...
Read more

SMS "Waiting for User Condition" 10036

Status Message ID 10036, or "Waiting for User Condition" can occasionally grind advertisements to a halt. Make sure the environment and user condition settings for a particular program have been configured appropriately and are not excluding a large portion of your intended audience. Related Articles SMS WQL for All Clients where Last Status is Waiting for User Condition SMS Status Message ID's About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . Visit EmmanuelTsouris.com for his latest projects. ...
Read more

Reading the SCCM Client GUID

Recently I encountered an issue with the SCCM client GUID missing from the  %windir%\SMSCFG.INI file. We wanted to write a script to put the GUID back, so I was asked how to read the SCCM Client GUID from a script or command line. VBScript: Set objWMIService = GetObject("winmgmts:\\.\root\ccm") Set colItems = objWMIService.ExecQuery("Select ClientID From CCM_Client") For Each objItem in colItems Wscript.Echo "Client ID" & objItem.ClientID Next WMIC via commandline: wmic /namespace:\\root\ccm path ccm_client get clientid PowerShell: Get-WMIObject -namespace root\ccm ccm_client clientid | Select-Object clientid Suggested Reading: MSDN Article on How to Get the Unique Identifier Value for a Client (Includes Code Snippets) MSDN Article on Using the Get-WMiObject Cmdlet About the Author Emmanuel Tsouris is a Systems Management veteran and Dev...
Read more