Powershell and Sharepoint Lists

Maybe I don't see the true beauty of sharepoint's API.  in my opinion it is needlessly complex.  After spending a day or so sifting through various powershell / sharepoint API information, I have come up with a couple of functions that simplify getting data out of sharepoint lists.  The functions are below.  I have saved the code into a file called sharepointlib.ps1 and i reference it when I need to speak to sharepoint.  You can do this with

. ./sharepointlib.ps1

If you don’t have the file in the local directory you will have specify a path instead of ./

 

once referenced, you pass it a sharepoint site URL and a List Name. I am using it as such:

$colListRows = get-SPList "http://SERVER/sites/SITE" "Calendar"

 

This gives me a collection of items on the calendar.  This only has the default view, a TODO on this would be to look up a view that matches the 'All Items' view.  It is also limited by a variable in there to only return 100 items.  not sure if that is a sorted list or not. You should be able to specify any list type.

 

Once you have your collection of list items, you can check how many items you have with:

$colListRows.data.ItemCount

You can pull out specific items with:

$colListRows.Data.Row

That will give you all the rows, may be overwhelming.  Limit it to the first row:

$colListRows.Data.Row[0]

Or, what I did was to spin it through a where to get a match on a day:

$colListRows.data.row | where {([datetime]$_.ows_EventDate).get_dayofyear() -eq $myDay.get_dayofyear()}

or match on title:

$colListRows.data.row | where {$_.ows_Title -like "*OnCall*"}

 

 

The difficulty i had in sharepoint was constructing the CAML query.  I assume this is an interface or legacy type issue.  I found SCOM and hell even Project server much easier, I was able to grab my objects and interrogate them directly.  It didn't seem to be the way in sharepoint.  Eventually, thanks to Ishai (http://www.sharepoint-tips.com/2007/02/how-to-use-getlistitems-web-service.html), I found a simple, essentially blank query could be passed.  Actually, looking at that URL again, this is more or less a powershell translation of his code.

 

One key is that you can pass $null for the view.  You can also create empty XML objects with the right name to pass into the query so I didn't really have to learn a bunch of CAML to make this work. 

 

Anyway, I am not a dev, I am an ops guy so I am sure there are suggestions.  Released under the license that says, feel free to use it but drop me a line if you have improvements or it helps you out.

 

 

Start the script below this line:

#set up some env variables.  this is largely to find wsdl and csc, maybe other libraries, i stole it from the web.

$env:VSINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio 9.0"

$env:VCINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio $obj\VC"

$env:DevEnvDir="$env:VSINSTALLDIR\Common7\IDE"

$env:FrameworkSDKDir="$env:VSINSTALLDIR\SDK\v2.0"

$FrameworkPath=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())

$env:FrameworkDir=$(split-path $FrameworkPath -Parent)

$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)

$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin;$env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin;$env:PATH"

$env:INCLUDE="$env:VCINSTALLDIR\ATLMFC\INCLUDE;$env:VCINSTALLDIR\INCLUDE;$env:VCINSTALLDIR\PlatformSDK\include;$env:FrameworkSDKDir\include;$env:INCLUDE"

$env:LIB="$env:VCINSTALLDIR\ATLMFC\LIB;$env:VCINSTALLDIR\LIB;$env:VCINSTALLDIR\PlatformSDK\lib;$env:FrameworkSDKDir\lib;$env:LIB"

$env:LIBPATH="$FrameworkPath;$env:VCINSTALLDIR\ATLMFC\LIB"

 

 

function get-SPList {

      #sharepoint helper function to get list data from sharepoint

      param(

            $strWebPath,

            $strListname

      )

      #connect to the web service and make my dll

      $void = wsdl $strWebPath/_vti_bin/lists.asmx

      $void += csc /t:library Lists.cs

      $void += [Reflection.Assembly]::LoadFrom("$pwd\lists.dll")

 

      #reference our new object type and connect to sp

      $ListWS = new-object Lists

      $ListWS.Credentials=[System.Net.CredentialCache]::DefaultCredentials

      $ListWS.url = "$strWebPath/_vti_bin/lists.asmx"

     

      #get our list by name

      $objList = $ListWS.GetList($strListname)

     

      #build xml annoyingness

      $objXML = New-Object System.Xml.XmlDocument

      $xQuery = $objXML.CreateElement("Query")

      $xViewFields = $objXML.CreateElement("ViewFields")

      $xQueryOptions = $objXML.CreateElement("QueryOptions")

     

      #you could do a CAML query here but we are just returning the first 100 rows.

      $intRowCount = 100

     

      #get the webID Guid

      $gWebID = Get-WebID($strWebPath)

     

      #get and return list items

      return $ListWS.GetListItems($objList.name, $null, $xQuery, $xViewFields, $intRowCount, $xQueryOptions, $gWebID)

}

 

function Get-WebID {

      #sharepoint helper function to get the guid of a sharepoing Web

      param ( $strWebPath )

 

      #Sharepoint hell

     

      #connect to the web service and make my dll

      $void += wsdl $strWebPath/_vti_bin/sitedata.asmx

      $void += csc /t:library SiteData.cs

      $void += [Reflection.Assembly]::LoadFrom("$pwd\sitedata.dll")

     

      #reference our new object type and connect to sp

      $SiteDataWS = new-object sitedata

      $SiteDataWS.Credentials=[System.Net.CredentialCache]::DefaultCredentials

      $SiteDataWS.url = "$strWebPath/_vti_bin/sitedata.asmx"

     

      #create a bunch of annoying variables so I can deal w/ sharepoint crap

      $arrwebmetadata = new-object _swebmetadata

      $arrwebwithtime = new-object _swebwithtime

      $listwithtime = new-object _slistwithtime

      $arrUrls = new-object _sFPUrl

      $roles = ""

      $roleusers = ""

      $rolegroups = ""

     

      #finally call my one line to get data

      $void += $SiteDataWS.GetWeb([ref] $arrwebmetadata, [ref]$arrwebwithtime, [ref]$Listwithtime, [ref]$arrUrls, [ref]$roles, [ref]$roleusers, [ref]$rolegroups)

       

      #my data has been stashed in teh $arrWebMetaData

      #return the guid

      return $arrwebmetadata.WebID

}

Powershell standard logging function

-->
I finally got around to creating a library for logging that I have been using. I am going to post another powershell script in a bit that uses this logging function so I figured I should get this up now.
-->

function Out-Log {

      #v.6 - cornasdf 20120325 - Indented verbose output

      #v.5 - cornasdf 20120210 - set logging to $env:HOMEDRIVE

      #v.4 - cornasdf 20120207 - fixed tab issue that broke copy and paste to console.

      #v.3 - cornasdf 20111109 - added newline and removed the timestamps from display

      #version .2 - cornasdf , 20090114

      #this script will allow for logging and screen output based on a requested verbosity level.

      #taking a cue from syslog, we are defining 0 as most critical errors.

      #by default in this script, verbosity is set as 1.  so we only print items specifically marked as 0 criticality to screen

      #default items come in as a log level of 1, ie they are not printed.  both of these can be overridden

      # all items are logged to a file based on the scriptname and run date at c:\toolkit\scripts\logs

      # it is expected that the default log level will be used for warnings and some informational messages

      # debug messages will be given log levels of 2 and higher as detailed below.

      # critical messages should be marked w/ a log level of 0

      #

      #

      #usage:

      # in your script you can include the function with:

      # . c:\toolkit\scripts\out-log.ps1

      #

      # you can then write all informational messages to log with

      # out-log <StringToLog> [LogLevel] [ForeGroundColor]

      #

      #simplest case (when in doubt, use this)

      # out-log "message to log"

      #

      #if you want to also ALWAYS print to screen you can set the message to level 0 with (use sparingly)

      # out-log "message to log" 0

      #

      #if you want make it print in RED on the screen (dependent on whether it will print to screen)

      # out-log "message to log" 0 RED

      #to change the level of logging that you want to see on screen, you can set a global variable 'verbosity'

      #this script will print log levels that are equal to or less than the variable '$verbosity'.

      #

      #a suggested usage is to accept a command line argument with verbosity level.

      #

      #best way to accept verbosity via cmd line is to add a param to the beginning of your script. it needs to be first line.

      # if the param below is added to your script, you can add "-v 3" to your command line to set the verbosity to 3,

      # this script prints all log items with a lower loglevel than the chosen verbosity.

      # if not set, we assume verbosity 0.

      # we then expect that only critical errors would be sent to log level 0.  default log level is 1.

      #param(

      #[string] $verbosity = 0

      #)

      # you can also set deeper log levels.  for example, if you want log level 1 to be warnings and information about script progress

      #    but you also want the option to enable/disable debug messages, you can log all your debug messages to a higher log

      #

      # out-log "debug string to log" 2

      #

      #if you then want to run your script such that you see debugging messages, you can run

      #./script.ps1 -verbosity 2

      #

      #note that you can use shortened command line arguments.  you just need enough to be unique so unless you

      # define another param that starts w/ 'v', you can use

      #./script.ps1 -v 2

      ##### BEGIN SCRIPT ######

      param(

            [string] $incomingString,

            [int] $logLevel = 1 ,

            $ForegroundColor = $host.ui.RawUI.ForegroundColor,

            $BackgroundColor = $host.ui.RawUI.BackgroundColor,

            [switch]$NoNewLine

      )

      #we are defining the log directory on all machines to be, use trailing "\"

      $logDirectory = "$($env:HOMEDRIVE)\logs\"

      #set your date

      $logDate = Get-Date -Format yyyyMMdd-HHmmss

      #have we defined verbosity?

      #if verbosity is not defined, we set it here as 0

      if ($verbosity -eq $null) {$verbosity = 0}

      #have we defined the logfile?

      if (-not (test-path variable:outlogfile)) {

            #if no, create it now based on todays datetime and progname

            #check for the log path, create if not found

            if (!(Test-Path -path $LogDirectory)) {

                  $tmp = New-Item $LogDirectory -type directory

                  Write-Host " ---> Created Log Directory at " $LogDirectory

            }

            #define name <ScriptName>-<Date>.log

            if($myInvocation.ScriptName -ne "") {

                  $scriptName =[IO.Path]::GetFileNameWithoutExtension($myInvocation.ScriptName)

            } else {

                  $scriptName = "NONAME"

            }

            $logName = $logDirectory + $scriptName + "-" + $logDate + ".log"

            Write-Host "Logging to: $logname" -ForegroundColor DarkBlue -BackgroundColorDarkYellow

            Set-Variable -Name OutLogFile -Value $logName -Scope script

      }

      #now we use the Logfile

      #if the log level is lower or equal to than the verbosity, we also spit to screen

      if ($logLevel -le $Verbosity) {

            Write-Host ("   " * $logLevel + $incomingString) -ForegroundColor$ForegroundColor -BackgroundColor $BackgroundColor -NoNewline:$NoNewLine

      }

      #date time stamp for logging

      $stringToLog = $logDate + ": " + "   " * $logLevel + $incomingString

      #finally, stick this in the log

      Out-File -filepath $outLogfile -inputObject $stringToLog -append

}