Need to find list items which are checked out AND have no version count for document libraries

Dec 16, 2013 at 8:44 PM
I have a client where many of its users are uploading files through explorer view. However, document versioning is enabled so a lot of the files are never checked it. I need to create a report which goes through all my sites within a collection, all the libraries and finds documents which are checked out but do not have version numbers (basically documents which have never been checked in).

I know I need to use getListItems but I'm not sure how to set up the CAML query to return this. I also don't need it to check lists like Calendars, Events or anything since this only applies to Document Libraries.

Anyone have an idea of how I could get a report which provides me the file name, site and library of documents which have no checked in version?
Dec 17, 2013 at 1:32 PM
Edited Dec 17, 2013 at 1:39 PM
I wrote this code which loops through the Site Collection, from each site, gets the list collection (only document libraries), from there looks through each item in the document library for items which are currently checked out but only have version 1.

This CAMLQuery doesn't work though: '<Query><Where><And><Eq><FieldRef Name="_UIVersionString" /><Value Type="Text">1.0</Value></Eq><IsNotNull><FieldRef Name="CheckoutUser" /></IsNotNull></And></Where></Query>'

It shows me only MY files which have no checked in version and other files which are fine. At this point, I think the only issue is getting the CAMLQuery right.
var fileCount = 0;
var limboData = [];

function XgetAllSites(){
  $().SPServices({
    operation: "GetAllSubWebCollection",
    async: false,
      completefunc: function(xData, Status){
      site = $(xData.responseXML);
      site.find('Web').each(function(){
        var siteName = $(this).attr('Title');
        var siteUrl = $(this).attr('Url');
        Xgetlists(siteUrl);
        console.log("At Sites: "+ siteName);
      });
    }
  });
  console.log(limboData);
  limboReport(limboData);
}
function Xgetlists(siteUrl){
    $().SPServices({
      operation: "GetListCollection",
      webURL: siteUrl,
      async: false,
        completefunc: function(xData, Status){
          $(xData.responseXML).find("List[ServerTemplate='101']").each(function(){
            var listName = $(this).attr('Title')
            var listId = $(this).attr('ID');
            XgetListItems(listId, siteUrl)
            console.log("At list: "+ listName);
          });
        }
    });
}
function XgetListItems(listId, siteUrl){
  $().SPServices({
    operation: "GetListItems",
    webURL: siteUrl,
    listName: listId,
    CAMLViewFields: "<ViewFields Properties='True' />",
    CAMLQuery: '<Query><Where><And><Eq><FieldRef Name="_UIVersionString" /><Value Type="Text">1.0</Value></Eq><IsNotNull><FieldRef Name="CheckoutUser" /></IsNotNull></And></Where></Query>',
    async: false,
    completefunc: function (xData,Status){
        $(xData.responseXML).SPFilterNode("z:row").each(function() {   
          var fileName = $(this).attr('ows_LinkFilename');
          var fileUrl = $(this).attr('ows_FileDirRef').split("#");
          var checkedTo = $(this).attr('ows_LinkCheckedOutTitle');
          var modified = $(this).attr('ows_Modified');

          limboData.push({
            fileName: fileName,
            fileUrl: fileUrl[1],
            checkedTo: checkedTo,
            modified: modified
          });

          fileCount++;
          console.log("At list items. File Found: "+ fileName);
      });
    }
  });
}
function limboReport(o){
        var header = "<tr><th>File Name</th><th>Location</th><th>Owner</th><th>Modified</th></tr>";
        $('#limboReport thead').append(header);

        var row = "";
        for (var i=0;i<limboData.length;i++){
         row += "<tr id='" + i + "'><td class='firstColumn'>" + o[i].fileName + "</td><td><a target='_blank' href='"+rootSite+'/'+o[i].fileUrl+"'>" + o[i].fileUrl + "</a></td><td>" + o[i].checkedTo+ "</td><td>" + o[i].modified + "</td></tr>";
        };
        $('#limboReport tbody').append(row);
      }