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

                              }

                        }

                  }

            }

      }

 }