Query: Identify ConfigMgr (SCCM) collections based on Active Directory OU membership

The question came up recently on how to identify which collections are based on Active Directory OU membership in Microsoft System Center Configuration Manager (SCCM) Current Branch

There are tons of guides on how to setup a collection based on OU membership.. but now that you have.. how many have you created?

This is quite simple direct SQL query:

SELECT [CollectionID]
  FROM [SMS_ABC].[dbo].[v_CollectionRuleQuery]
  where QueryExpression like ‘%System_OU%’


*  not all collection memberships need to be based on System OU.  Varitations will be needed based on requirements

* The above query is based on a view.  If you want to see the SQL you would query the table Collection_Rules_SQL

Useful Tip

The SQL table Collection_Rules_SQL also has the WQL versions of the same query.  This is extremely useful for figuring out the SQL tables involved with any WQL query from collections or the Query node.  You can also get this translation from the SMSProv.log file but that is like searching for a needle in a hay stack while the hack stack is getting bigger and may disappear in a roll over.


A nice bit of documentation at Creating Custom Reports by Using SQL Server Views in System Center 2012 Configuration Manager

Otherwise, happy spelunking!


Music Library Cleaning with PowerShell – Removing Missing Files from iTunes


At the bottom of this post I originally wrote a VBS that deletes files out of ITunes if the location does not exist.  I did the rewrite into PowerShell…

(new-object –com itunes.application).LibraryPlaylist.Tracks | ?{ $_.Location -eq $null } | %{ $_.Delete() }


The PowerShell above only removes the item out of ITunes if the location is NULL.  I need to put in a step to check to see if the location is valid.

ITTrackKindFile = 1

deletedTracks = 0

on error resume next

set iTunesApp = WScript.CreateObject("iTunes.Application")

set mainLibrary = iTunesApp.LibraryPlaylist

set tracks = mainLibrary.Tracks

Set FSO = CreateObject("Scripting.FileSystemObject")

for each currTrack in tracks

	' is this a file track?

	if (currTrack.Kind = ITTrackKindFile) then

		' yes, does it have an empty location?

		if (currTrack.Location = "") 		then 

			' yes, delete it

			wscript.echo currtrack.name & " - " & currTrack.Location


			deletedTracks = deletedTracks + 1


			'wscript.echo currTrack.Location

			if not fso.fileExists(currTrack.Location) then

				wscript.echo currTrack.name & currTrack.Location


			end if	

		end if

	end if


wscript.echo "-----------------------------------------------------"

wscript.echo "deletedTracks = " & deletedTracks

Music Library Cleaning with PowerShell – Identifying Old MP3 Files with Low Bitrates


Over the past couple of years I travel quite a lot.  As such, I tend to load up on digital format. However, some of my older files have some nasty names or bad quality.

This has led to me to start grooming my 15,000+ song catalog.  That number does not include another 15,000+ audio book files.  I love to listen to books while running.

As I was going through the folders of CD rips, iTunes purchases, and other sundry file droppings… I was feeling a bit overwhelmed.

Trying to scope the issue I went for the following categories

  • ITunes Music
  • Files below a certain Bitrate Threshold
  • Artists I did not enjoy
  • CD Tagging

Today I am going to talk about how I used PowerShell to identify files below a certain bitrate threshold.   Example, I want to get all MP3 files with a bit rate less than or equal to 100.  Then I want to delete them… as they sound even worse on digital players (like the iPhone)

  1. Run the PowerShell Function Get-Mp3FilesLessThan with up to three inputs
    1. Inputs
      1. Directory
      2. BitRate
      3. File Extension
    2. Example
      1. Get-Mp3FilesLessThan \\Server\Music 100 *.mp3 | Format-Wide
    3. Output
      1. List of all files where criteria are matched
  2. Delete result site
    1. Get-Mp3FilesLessThan \\Server\Music100 *.mp3 | Remove-Item -WhatIf
      1. I like using –WhatIf because Remove-Item by default does not show results
      2. Remove the –WhatIf to commit the change


function Get-Mp3FilesLessThan( [string]$directory = "$pwd", [int]$minimumBitrate = 32, [string]$FileTypeExt = "*.mp3" ) {
  $shellObject = New-Object -ComObject Shell.Application
  $bitrateAttribute = 0

  # Find all mp3 files under the given directory
  $mp3Files = Get-ChildItem $directory -recurse -filter $FileTypeExt
  foreach( $file in $mp3Files ) {
    # Get a shell object to retrieve file metadata.
    $directoryObject = $shellObject.NameSpace( $file.Directory.FullName )
    $fileObject = $directoryObject.ParseName( $file.Name )

    # Find the index of the bit rate attribute, if necessary.
    for( $index = 5; -not $bitrateAttribute; ++$index ) {
      $name = $directoryObject.GetDetailsOf( $directoryObject.Items, $index )
      if( $name -eq 'Bit rate' ) { $bitrateAttribute = $index }

    # Get the bit rate of the file.
    $bitrateString = $directoryObject.GetDetailsOf( $fileObject, $bitrateAttribute )
    if( $bitrateString -match '\d+' ) { [int]$bitrate = $matches[0] }
    else { $bitrate = -1 }

    # If the file has less than or equal to the desired bit rate, include it in the results.
    if( $bitrate -le $minimumBitrate ) { $file }

MyITForum: Then of an era

I have been an avid user, contributor, speaker, and blogger on www.myitforum.com since 2005.  It has been an amazing journey.  However with the most current owners a lot of great content is disappearing.  As such I am attempting to grab content before it disappears completely.

The email lists are by far the best part still.

As such sorry for all the ancient posts coming over in the next few days.



Google: Local Guides level 5 – You’re a Local Guides pro

Level 5: 500+ Points

As a Level 5 Local Guide, you can:

Get access to test new Google products before public release
Apply to attend our Level 5 Local Guides summit


This is the highest level you can currently achieve with Google Local Guides.  Other levels have nice Benefits.  Like 1TB of Google Drive storage.  I travel a lot for work and I tried to writing on a lot of different sites like Travelzoo, yelp, etc.  I fixated on maps.google.com mainly because it is ubiquitous and fairly easy to add info.  I hope you enjoy one of my piratical posts on any of the several hundred locations.



Resolved: Microsoft Office 2010 Copy and Paste Stopped Keeping Source Formatting if Skype Click to Call is Installed

Written in 2011 – http://myitforum.com/myitforumwp/2012/06/19/resolved-microsoft-office-copy-and-paste-stopped-keeping-source-formatting-if-skype-click-to-call-is-installed/


I noticed recently that my copy and paste in Microsoft Office 2010 stopped keeping the source formatting.  It was only allowing the paste of text only.  If you were to check for paste special it would show only a capital A.

I noticed that if you closed all internet browsers except Firefox the issue would resolve.

Turns out SKYPE Click to Call Plug in is causing the issue

Uninstall SKYPE Click to Call.

Everything works well again!

Marriott and SPG: Better together if you have Status

As of September 2016 Marriott purchased SPG.  If you have status  with any of the following (high enough) then you have status at them all!

  • Marriott
  • SPG
  • Delta
  • United

Marriott Platinum or SPG Platinum gives you the other. http://Members.marriott.com

Marriott Platinum Status gives you United Silver – https://mrrewardsplus.com

United Mileage Plus Gold or Higher Gives you Marriott Gold – https://secure.unitedmileageplus.com/reg/rewards_plus/index.htm

SPG Platinum gives you Delta Silver –https://www.starwoodhotels.com/preferredguest/account/benefits/delta/confirm.html

Delta  Diamond or Platinum Medallion  gives you SPG status – https://www.delta.com/profile/spgLanding.action

Uber gives you SPG points – https://www.spgpromos.com/uber/?EM=VTY_SPG_UBER_PROMOTION?iataNumber=33707800&wps=96635464&siteID=76t2eiQKcUU-gphD_XQGfwK95PBXt1sp4A

Basically it is a very good day for domestic travel.