Exporting songs in Windows Media Player by star rating

So, I used this some forever ago to move song ratings from iTunes to Windows Media Player with a powershell function.  Now I wanted to export the ratings to a file so I have that list outside of wmp.  Annoyingly, i didn't see a way to export the song list, saving the playlist just saved the search terms.  Meh, powershell to the rescue, yet again...

This function will export the name of all files w/ ‘-starcount’ stars.  Could be trivially modified to export other details.

function get-playlistbyStars {

      param ($starcount = $(throw "need -starcount")

           )

     

      #get WMP

      $wmp = New-object -COM WMPlayer.OCX

      $WMPLibrary= $wmp.mediaCollection

      #set vars

      $rated = 0

      $processed = 0

      $added = 0

      $unrated = 0

      $1star = 0

      $2star = 0

      $3star = 0

      $4star = 0

      $5star = 0

      $defstar = 0

      $ctr = 0

      $allsongs = $WMPLibrary.getall()

      $colOut = @()

      for ($i = 0; $i -lt $allsongs.count; $i++) {

            $processed++

            $song = $allsongs.Item($i)

            if (@(".jpg",".png") -notcontains ([System.IO.FileInfo]$song.sourceURL).Extension ) {

                  switch ($song.getiteminfo("UserRating")) {

                        "0" {

                              $unrated++

                              if ($starcount -eq 0) {

                                    $colOut += $song.sourceURL

                              }

                        }    

                        "1" {

                              $1star++

                              $rated++

                              if ($starcount -eq 1) {

                                    $colOut += $song.sourceURL

                              }

                        }

                        "25" {

                              $2star++

                              $rated++

                              if ($starcount -eq 2) {

                                    $colOut += $song.sourceURL

                              }

                        }

                        "50" {

                              $3star++

                              $rated++

                              if ($starcount -eq 3) {

                                    $colOut += $song.sourceURL

                              }

                        }

                        "75" {

                              $4star++

                              $rated++

                              if ($starcount -eq 4) {

                                    $colOut += $song.sourceURL

                              }

                        }

                        "99" {

                              $5star++

                              $rated++

                              if ($starcount -eq 5) {

                                    $colOut += $song.sourceURL

                              }

                        }

                        default {

                              #so I have something to query for random nums

                              $defstar++

                              if ($starcount -eq 42) {

                                    $colOut += $song.sourceURL

                              }

                        }

                  }

                  #so I have something to watch

                  $ctr++

                  if ($ctr%500 -eq 0) { Write-verbose $ctr}

            }

      }

      #output results

      Write-verbose "Processed:  $processed"

      Write-verbose "Rated:  $rated"

      Write-verbose "Added:  $added"

      Write-verbose "1:  $1star "

      Write-verbose "2:  $2star "

      Write-verbose "3:  $3star"

      Write-verbose "4:  $4star "

      Write-verbose "5:  $5star"

      Write-verbose "def:  $defstar"

     

      $colOut

}

     

get-playlistbyStars 3 | out-file –filepath .\3starsongs.txt

SQL and Powershell: Attach all database mdf files in a directory

Hey all,

I didn’t find this elsewhere on the web and I needed it. Since I actually had to do work, i figure I should give it out and spare somebody else that pain.

As part of a failover plan, I am synchronizing directories of sql files. In a failover, I am going to need to bring up a group of databases in a specific directory. The script below will take in a directory name and a SQL instance. It will get all the .mdf files in the directory and compare them against the database files attached to the SQL instance. Any files that are not attached already are attached, extrapolating the filename from the .mdf file. The MAJOR ASSUMPTION here is that you want to name your Database the same name as the .mdf file (without the .mdf). Several of my dbs have multiple files so I read the .mdf to get all the files it knows about. Another major ASSUMPTION is that you are replicating to/attaching from the same file paths on your new server.

I use my out-log function. if you don’t want to use it, replace out-log with write-host in the below script.

function AttachMDFsInDir {

      param (

            $dir = $( throw "Must specify -dir" ),

            $instance  = $( throw "Must specify -instance" ),

            $verbosity = 1 # for the out-log function.

            )

      out-log "Starting AttachMDFsInDir with dir: $dir and inst: $instance"

      $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)

      out-log "Connected to $($srv.Name)"

     

      #get a list of all attached database file names

      $colAttachedMDFs = @()

      foreach ($db in $srv.Databases ) {

            foreach ($fg in $db.Filegroups) {

                  foreach ($file in $fg.Files) {

                        out-log "Adding to `$colattachedMDFs: $($file.Filename)" 2

                        $colAttachedMDFs += $file.Filename

                  }

            }

      }

      out-log "Found $($colAttachedMDFs.count) mdfs attached in $instance"         

                 

      #foreach mdf in $groupSQLDir

      $colMDFsToAttach = @()

      foreach ($mdf in dir "$dir\*.mdf") {

            if (-not ($colAttachedMDFs -contains $mdf)) {

                  out-log "Adding to `$colMDFsToAttach: $($mdf.Fullname)" 2

                  $colMDFsToAttach += $mdf.FullName

            }

      }

      Remove-Variable mdf

      Remove-Variable file

     

      out-log "Found $($colMDFsToAttach.count) mdf(s) in the directory that are unattached"           

      foreach ($mdf in $colMDFsToAttach) {

            #determine name to restore as from mdf file name.

            $DBNameFromFile = ([System.IO.FileInfo]$mdf).Basename

            out-log "Named the db $DBNameFromFile based on the mdf: $mdf"

            #get list of files

            $colDBFiles = $srv.EnumDetachedDatabaseFiles($mdf)

            out-log "$mdf specifies $($coldbfiles.count) files to attach."

            out-log $colDBFiles 2

           

            #add to collection

            $files = new-object system.collections.specialized.stringcollection

            foreach ($file in $colDBFiles) {

                  $files.Add($file)

            }

            out-log "Ready to attach db: $DBNameFromFile w/ $($files.count) files."

            #attach database

            $srv.AttachDatabase($DBNameFromFile,$files)

      }

}

As a corollary to the above, i needed to check which of my dbs on the running servers did not have the same name as their .mdf. you can use this loop.

$instances = @("SERV\Inst1","Serv\INST2") )

foreach ($instance in $instances) {

      $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)

      ""

      "------------------------"

      $srv.Name

      "------------------------"

     

      foreach ($db in $srv.Databases ) {

            foreach ($fg in $db.Filegroups) {

                  foreach ($file in $fg.Files) {

                        $Fileinf = [System.IO.FileInfo]$file.filename

                        if ($Fileinf.extension -eq ".mdf" ) {

                              if ($Fileinf.BaseName -eq $db.name) {

                                    #"File: $($Fileinf.Basename) matches $($db.name)"

                              } else {

                                    "Filename: $($Fileinf.Basename) DOES NOT MATCH DB Name: $($db.name)"

                              }

                        }

                  }

            }

      }

 }

SCCM Child Primary site not replicating Update lists or deployments

We ran across an issue this weekend with one of our SCCM child primary sites not replicating deployments or software update lists correctly.  2 of 3 primary sites were replicating fine but one primary was not getting new items.  For this article, I will represent my central site as CEN and the non working primary site as PRI (imaginative, I know).

After several hours with the configuration manager logs, I had pretty much tracked it down to bad configuration items (patches) but I was stuck on what to do after trying various sync options, mostly detailed here:  http://blogcastrepository.com/blogs/brian_tucker/archive/2009/06/18/how-to-resolve-site-replication-issues-with-sccm-2007-sp1-r2.aspx.

I called MS support and he gave me a set up queries to help figure out what was going on.

At the end of the day, the problem can be seen in objreplmgr.log and you see sets of lines like:

Processing replication file C:\Program Files\Microsoft Configuration Manager\inboxes\objmgr.box\INCOMING\Retry\CEN_48964.CID in retry.                SMS_OBJECT_REPLICATION_MANAGER               6/27/2011 9:42:31 AM    5640 (0x1608)

Referenced configuration items are not available yet: http://schemas.microsoft.com/systemsmanagementserver/Site_A4F5CBC7-3D34-4DC1-A024-C496694647C5/SUM_914207c8-1e32-4e4e-acee-7ebdce4568b2/2(2)(6);                SMS_OBJECT_REPLICATION_MANAGER               6/27/2011 9:42:31 AM    5640 (0x1608)

Failed to insert Object e5c2a931-5c7c-4c0b-843a-343525f73e3d from replication file C:\Program Files\Microsoft Configuration Manager\inboxes\objmgr.box\INCOMING\Retry\CEN_48964.CID.                SMS_OBJECT_REPLICATION_MANAGER               6/27/2011 9:42:31 AM    5640 (0x1608)

Note the unique package name highlighted in red above.  This is the key string. 

We did do an ‘attempt 1’ that was unsuccessful but may be worth trying.  In this, we extract just the GUID of the CI and update the date in the CEN database and hope it replicates down.  Get the GUID from the highlighted string above as everything after the SUM_.  In our case it would be 914207c8-1e32-4e4e-acee-7ebdce4568b2.  In the query below, we have multiple CIs identified, you can put as many as you want in the query (gathered from different log lines).

Update ci_configurationitems

set datelastmodified = getdate()

where ci_uniqueid in (‘914207c8-1e32-4e4e-acee-7ebdce4568b2’,' c4fca437-b931-48fc-a6bf-6cc7d0aefc2d’)

This didn’t do anything useful in our case so we continued on to ‘Attempt 2’.

If you take your whole string and do a select in your SCCM database on both the primary and the central sites:

SELECT * FROM ci_sdmpackages where SDMPackageName in (‘Site_A4F5CBC7-3D34-4DC1-A024-C496694647C5/SUM_914207c8-1e32-4e4e-acee-7ebdce4568b2’)

Take note of the isDeleted and the SDMPackageVersion columns.  For me, 95% of the items showing up in the logs were showing up as deleted in CEN but not in PRI.  This can be fixed by running an update like:

update ci_sdmpackages

   set Isdeleted = 1

   where SDMPackageName = ' Site_A4F5CBC7-3D34-4DC1-A024-C496694647C5/SUM_914207c8-1e32-4e4e-acee-7ebdce4568b2’ and SDMPackageVersion = '3'

Note that you will change the GUID and the SDMPackage version.  You want to update your PRI site to match CEN.

One your DB is updated, you can move your replication file as ID’d above (CEN_48964.CID in this case) from the objmgr.box\incoming\retry to objmgr.box\incoming.  Watch  your objreplmgr.log for success.

Now, that worked but I had 110 CIDs to work through and I heartily ascribe to larry wall’s 3 virtues.  in order to shorten my workload a bit, this is how I did it.  I didn’t have any good way to communicate between sites, unfortunately.  If I did, I would have done this much more directly.  I was on a bit of a deadline to get this up so I went for speed over elegance.

1.     Get all the guids.  I ran the following against the objreplmgr.log to pull out just my relevant configuration item IDs.  I ran this against a copy w/ just one run of ‘failures’ initially but we do sort out unique so you can probably just run it against the real thing.

$a = get-content .\objreplmgr.log | where {$_ -match "Referenced configuration items are not available"}

$ciList = @()

Foreach ($line in $a) {

   $line -match ".*(Site_.*/SUM_.*?)/.*"

   $ciList += $Matches[1]

}

2.     I tried to strip out duplicates.  On my first run this only saved 2 items, next run saved me much more.  If you run against the whole log, this is more important.

$ciListSorted = $ciList | sort –Unique

3.     Create my sql query:

$sqlQuery = "SELECT * FROM ci_sdmpackages where SDMPackageName in ("

for ($i=0; $i -lt $ciListSorted.length - 1; $i++) {

   $sqlQuery += "'$($ciListSorted[$i])', "

}

$sqlQuery += "'$($ciListSorted[$ciListSorted.Length-1])')"

4.     Run the sql query against both databases.  I have this function in my standard library that I stole some time ago:

function query-SQL {

   param(

         $DBInstance,

         $Datatabase,

         $Query)

  

   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

   $SqlConnection.ConnectionString = "Server = $DBInstance; Database =$Database; Integrated Security = True"

   $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

   $SqlCmd.CommandText = $Query

   $SqlCmd.Connection = $SqlConnection

   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

   $SqlAdapter.SelectCommand = $SqlCmd

   $DataSet = New-Object System.Data.DataSet

   $SqlAdapter.Fill($DataSet)

   $SqlConnection.Close()

   return $DataSet.Tables

}

Run against my Primary database

$colPRI = query-SQL -Datatabase $database -DBInstance $dbinstance -Query $sqlQuery

$PRI = @()

Foreach ($line in $colPRI[1]) {

   $PRI += "PRI,$($line.isDeleted),$($line.SDMPackageName),$($line.SDMPackageVersion)"

}

$PRI > PRI.csv

For the CEN site, I manually copied the sql query over w/

$sqlQuery | Out-File sqlquery.txt

Created the text file on the other side and brought it back in and ran it like so:

$sqlquery = Get-Content .\sqlquery.txt

$colCEN = query-SQL -Datatabase $database -DBInstance $dbinstance -Query $sqlQuery

$CEN = @()    

Foreach ($line in $colCEN[1]) {

   $CEN += "CEN,$($line.isDeleted),$($line.SDMPackageName),$($line.SDMPackageVersion)"

}

$CEN > CEN.csv

Then, I brought my CEN.csv back over to PRI and brought it back to my powershell env.

$CEN = Get-Content .\cen.csv

Now we have $CEN and $PRI that are effectively .csv files in a variable.  Check your lengths, do some spot verification that they are the same length and have the same IDs in the same order, etc.  We did sort them above and everything should be fine and loop below does some checking but, well, defense in depth.  Did you remember to back up your databases?  Not my problem if not.

This loop spins through your two arrays and sets the PRI database to match what is in the CEN database.  Note the query-sql line, you will need $database and $DBInstance defined already.

for ($i = 0; $i -lt $PRI.length -1; $i++) {

   $site,$CenIsDel,$CenID,$CenVersion = $CEN[$i].split(",")

   $site,$PriIsDel,$PriID,$PriVersion = $PRI[$i].split(",")

   if ($CenIsDel -eq "True") { $CenIsDel =1 } else { $CenIsDel = 0}

   if ($PriIsDel -eq "True") { $PriIsDel =1 } else { $PriIsDel = 0}

   #Make sure our lines are the same

   if (-not $CenID -eq $PriID) {

         throw ("iteration $i, $CenID not equal to $PriID")

   }

  

   if (($PriIsDel -ne $CenIsDel) -or ($PriVersion -ne $CenVersion)) {

         $query = "update ci_sdmpackages set Isdeleted = $CenIsDel where SDMPackageName = '$CenID' and SDMPackageVersion = '$CenVersion'"

         "Setting $PriID to $CenisDel and $CenVersion"

         query-sql -database $database -dbinstance $dbinstance -query $query

   } else {

         "$PriId already matches central"

   }

}

After this, I dropped all the files from objmgr.box\incoming\retry back into objmgr.box\incoming.  Success!  Well, mostly.  I still had about 8 bad CIDs.  Looking in the log, some of them were due to lines that had two CIs mentioned. 

Referenced configuration items are not available yet: http://schemas.microsoft.com/systemsmanagementserver/Site_A4F5CBC7-3D34-4DC1-A024-C496694647C5/SUM_40d3d142-5ea7-463c-a7c1-9d34cdd5a7fc/1(1)(6);http://schemas.microsoft.com/systemsmanagementserver/Site_A4F5CBC7-3D34-4DC1-A024-C496694647C5/SUM_c4fca437-b931-48fc-a6bf-6cc7d0aefc2d/1(1)(6);

My regex only accounts for 1.  Rather than figure out a better regex, I ran through the steps above again I gathered the 3 CIs that I had that were specified as the second on a line.  Then I ran through the steps above and manually added my 3 CIs to the $ciList, when I was at that step.  Moved the \retry items back into the box and all were consumed successfully.  Gave it about 15 minutes and site replication is working again.  W00t!