locked
PowerShell query to return recently approved items? RRS feed

  • Question

  • I have a requirement for a PowerShell script to retrieve all documents in a library which have had a new version approved in the last X days.  I can't simply grab the latest version, since that version may not have been approved.  I also can't grab all with status=approved, as that would ignore items that were approved but then edited but not yet approved.

    Brute force wise, it would look like

    1. loop through entire library
    2. foreach doc, loop through its versions
    3. determine which (if any) of its versions was the most recent version approved in the last X days
    4. note the guid of that version (add it to some list)

    Sort of like http://stackoverflow.com/questions/1366242/find-latest-approved-version-of-an-splistitem .

    But then as I say I'd have to loop through all items in the library to accomplish my goal.  Is there a more elegant way perhaps with SPQuery/CAML ?  The tricky thing is I'm trying to retrieve a set of versions across different items, not sure how I can do that.

    Thanks

    • Moved by Mike Walsh FIN Monday, January 10, 2011 8:34 PM Powershell (From:SharePoint - Development and Programming (pre-SharePoint 2010))
    Monday, January 10, 2011 8:27 PM

All replies

  • Sort of a solution (at least mitigates the brute force) -

    $site = get-spsite http://mysite                             
    $web = $site.OpenWeb()                                              
    $list = $web.Lists["Documents"]                                          
    $freq = 30 # in minutes
    $now = get-date
    $lastrun = $now.AddMinutes($freq)
    $lastrun_converted = [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($now.AddMinutes(-$freq))
    $myq = "<Where><Geq><FieldRef Name='Modified'/><Value Type='DateTime' 
    IncludeTimeValue='True'>$lastrun_converted</Value></Geq></Where>"
    $query=new-object Microsoft.SharePoint.SPQuery
    $query.ViewAttributes = "Scope=`"Recursive`""
    $query.Query=$myq
    $result = $list.GetItems($query)
    foreach ($r in $result) { 
     #write-host $r.Url : $r.GetFormattedValue("Modified")
    $rdt = [system.DateTime]::Parse($r.Properties["vti_lmt"])
     write-host $r.Url : $rdt
    }
    write-host
    write-host "     Now: " $now
    write-host "Previous Run: " $lastrun
    write-host $result.Count items modified in the last $freq minutes ...
    write-host
    The problem is it just grabs items that have been changed for any reason, so you would have to loop through the versions of each to find if there was one approved in the last $freq minutes, but a big improvement over parsing the whole library.  Might be a better way?
    Monday, January 10, 2011 11:55 PM