Microcode: Getting DVR MetaData with Search-WindowsDesktop

In the previous post, i explained that I was trying to find a way to get at the information about my TV shows, and introduced you to some of the thought process that went into finding out information about my DVR.  I then solved the most important portion of my DVR problem (extracting out the metadata) in a way that was flexible enough to apply to be used for other potential projects, like Getting Photo Metadata or Getting Contact Metadata.

Here's the Search-WindowsDesktop function again:

function Search-WindowsDesktop($fields, $filter)
    $connection = New-Object -comObject "ADODB.Connection"
    $recordSet = New-Object -comObject "ADODB.RecordSet"

    $null = $connection.Open("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';") > $null

    $ofs = ","
    $query = "SELECT $fields"
    $query += " FROM SYSTEMINDEX $filter"
    $null = $recordSet.Open($query, $connection)

    if ($recordSet.EOF) { return }

    while (-not $recordSet.EOF) {       
        $result = New-Object Object       
        foreach ($field in $fields) {
            $result | Add-Member NoteProperty $field $recordSet.Fields.Item($field).Value

    $null = $recordSet.Close()
    $null = $connection.Close()

    $connection = $null
    $recordSet = $null

I also introduced the way I think solve problems by looking at the smaller problems they contained.  My starting point was that I was trying to prune my Windows Media Center dvrms files.  I could have gone about getting rid of Windows Media Center files in a less elegant way, but I would have regretted it.  For instance, I could have pruned the files just on size, but I would have Lost Lost.   But going down the path of getting metadata lets me do more related tasks.

I strongly believe that whenever problem solving, it is key to examine not just the problems that you are working with, but to look at the related problems.  The root problem in this case was that dvrms files are my appetite for video on demand are both large enough to flood my disk.  If I pruned my collection on size, not only would I have lost Lost, but I would only have the option of losing the files forever.  By getting the DVR metadata, I open up the door to a number of more promising paths:

  • I can back up certain shows to other hard drives
  • If I can find a way to remove commercials
    • I can burn a DVD of the files
  • If I can find the right API,
    • I can convert the files to another file format

Getting the metadata solves the problem in a better and more flexible way than does chopping by file size.  Researching it also exposed me to other cool things that I can do, and give me a reference to use while creating my DVR Metadata script.  Using PowerShell, I tried running queries one by one for each of the properties on the Recorded TV reference to discover which ones work and determined if the return values were helpful.

Here's the result of my efforts:

function Get-RecordedTV() {
  Search-WindowsDesktop 'System.Title',
    'System.RecordedTV.RecordingTime' "WHERE System.RecordedTV.ChannelNumber IS NOT NULL"

I know it really doesn't look like it, but that can be written as a one-liner.  By using an existing system and writing a generic function, I was able to write this Get-RecordedTV function simply by providing the data I needed to my Search-WindowsDesktop function.

Since the Search-WindowsDesktop function returns objects with property names, it is possible to use a number of built-in PowerShell commands to help you look through the data.

Here are the Cmdlets you can use to explore objects:

Group-Object Creates groups of objects on a type or expression
Select-Object Selects properties from an object or make new ones on the fly
Sort-Object Sort a list of objects by a property
Where-Object (or ?) Filters the objects by an expresson
Foreach-Object (or %) Performs an action on each of the input objects

One quick thing I can see this way is what TV station I watch the most:

Get-RecordedTV | Group-Object 'System.RecordedTV.StationCallSign' | Sort Count -Descending

Another thing I can do with a simple change is group by Parental Rating instead

Get-RecordedTV | Group-Object 'System.ParentalRating' | Sort Count -Descending

And of course, I can sort on the size and the time recorded:

Get-RecordedTV | sort 'System.RecordedTV.RecordingTime' -descending

Get-RecordedTV | sort 'System.Size' -descending

Now I have the potential to build a much more flexible TV pruning solution.  In a future post, I'll show how you can resolve the ItemURL to a real file and, finally, how to prune the files you don't want by any criteria you'd like.

Hope this helps,

James Brundage