-->

My life was once filled with fancy server-side scripts, but now I'm just another SharePoint consultant.

Thursday, January 14, 2010

The SharePoint / Perl Connection (Part 1)

Why on earth would these two disparate camps ever meet? Perl is 15 million years old, right, and SharePoint is the bane to system administrators everywhere. The small “SharePerl” audience includes me, and two or three really old techs that still use land line phones.

But Perl can make a nice replacement for SharePoint Timer Jobs and can be easier to debug than a Visual Studio developed solution. (Okay, whatever… My .NET experience is only deep enough that I can spell it.)

Setup

I have ActivePerl installed on an XP VM, which I use for all my script development. ActivePerl comes with a Perl Package Manager, which allows you to install any one of 12,000 Perl MODs with relative ease. Over the years I’ve installed a good number of these MODs ranging from simulating poker hands to creating PDF files script-o-matically. When IE finally supports SVG, my cachet will rise dynamically, thanks to Perl MODs.

Perl does not need to be installed on a SharePoint server. Don’t do that. That’s crazy. Perl can be run from any desktop, with the caveat: If you want to schedule Perl scripts to run at certain times, the machine has to be on, with a network connection. The interaction from Perl to SharePoint happens thanks to SharePoint web services and Perl’s LWP MOD.

LWP

LWP stands for “little www-browser for Perl”. It’s a collection of functions and classes that allow Perl to become a simulated web-browser. Ever wonder how email scraping robots work? I think I can safely say that LWP is involved. LWP is in the hacker tool category of “fun, fun, fun”. Go find a poll or survey that doesn’t restrict multiple entries by IP address, and script up an iterative LWP session. Okay, there’s still a law about self-incrimination, right?

LWP uses a Perl site level authentication library specifically tailored for NTLM:

C:\Perl\site\lib\Authen\NTLM.pm

Unfortunately, the library used by LWP for NTLM authentication is not compatible with IIS6 or 7. NTLM.pm needs to be hacked to send parts of the authentication packet in Unicode:

Edit NTLM.pm and find the line:

$domain = substr($challenge, $c_info->{domain}{offset}, $c_info->{domain}{len});
and replace that code with (or better yet, comment out the code above and insert the code below):

$domain = &unicode($domain);

SOAP::Lite


SOAP::Lite is a MOD that is subtitled, “Perl's Web Services Toolkit”. If you’ve ever had the privilege of working with SharePoint’s Web Services, you’ll know they can handle almost anything you’ve ever wanted to do with a SharePoint site. This functionality is accomplished via SOAP calls.

When you load a web part page into SharePoint Designer (2007), have you ever seen the SOAP calls churning in the bottom left hand corner?

We’ll use this MOD to formulate SOAP calls to our SharePoint Web Services.

Credit, Where Due


Most of what I learned about the SharePoint / Perl connection, I got from a blog post from a Londoner named James who blogs at
WWW.SQUISH.NET. His post entitled, “Talking to SharePoint Lists with Perl” is a gem. James’ bio mentioned he was working in banking, and that he loves Lotus Notes. Well, God bless him.

Let’s Roll


I’m going to just paste the Perl code here:

  1: use LWP::UserAgent;
  2: use LWP::Debug;
  3: use Data::Dumper;
  4: use SOAP::Lite on_action => sub { "$_[0]$_[1]"; };
  5: import SOAP::Data 'name', 'value';
  6: our $sp_endpoint = 'http://shareperl/sites/wef/_vti_bin/lists.asmx';
  7: our $sp_domain = 'shareperl:80';
  8: our $sp_username = 'PIMPS\\wef';
  9: our $sp_password = 'Pa$$word16';
 10:
 11: $debug = 0;
 12:
 13: if ($debug) {
 14:     LWP::Debug::level('+');
 15:     SOAP::Lite->import(+trace => 'all');
 16: }
 17:
 18: my @ua_args = (keep_alive => 1);
 19: my @credentials = ($sp_domain, "", $sp_username, $sp_password);
 20: my $schema_ua = LWP::UserAgent->new(@ua_args);
 21: $schema_ua->credentials(@credentials);
 22: $soap = SOAP::Lite->proxy($sp_endpoint, @ua_args, credentials => \@credentials);
 23: $soap->schema->useragent($schema_ua);
 24: $soap->uri("http://schemas.microsoft.com/sharepoint/soap/");
 25:
 26: $lists = $soap->GetListCollection();
 27: quit(1, $lists->faultstring()) if defined $lists->fault();
 28:
 29: sub lists_getid
 30: {
 31:     my $title = shift;
 32:     my @result = $lists->dataof('//GetListCollectionResult/Lists/List');
 33:     foreach my $data (@result) {
 34:         my $attr = $data->attr;
 35:         return $attr->{ID} if ($attr->{Title} eq $title);
 36:     }
 37:     return undef;
 38: }
 39:
 40: sub lists_getitems
 41: {
 42:     my $listid = shift;
 43:     my $in_listName = name('listName' => $listid);
 44:     my $in_viewName = name('viewName' => '');
 45:     my $in_rowLimit = name('rowLimit' => 99999);
 46:     my $call = $soap->GetListItems($in_listName, $in_viewName, $in_rowLimit);
 47:     quit(1, $call->faultstring()) if defined $call->fault();
 48:     return $call->dataof('//GetListItemsResult/listitems/data/row');
 49: }
 50:
 51: my $list_id = lists_getid('Disk Space');
 52: print "List ID is: $list_id\n";
 53: my @items = lists_getitems($list_id);
 54: foreach my $data (@items) {
 55:     my $attr = $data->attr;
 56:     print Dumper($attr);
 57: }
Here’s the output from the command-line:

Perl_Run

And here’s the view of the SharePoint list:
SharePoint_Run

Much more to come in later posts, but I hope you get an idea of where you can go using Perl to interface with a SharePoint list…

35 comments:

  1. Sorry 'bout the formatting... I need to host my own blog already.

    ReplyDelete
  2. Hi John,

    I too saw the squish.net article and have put it to very good use. I'm pulling information straight out of a document library. However where things break down for me is that I need to query version history too.

    Pointing at versions.asmx and trying to use GetVersions causes a 500 internal server error. Must admit I'm puzzled at this, because looking at the XML being generated by SOAP::Lite it matches what versions.asmx wants as far as I can see.

    Pretty sure I'm sending the right things as I have a C# version of this too and it works. Can't use that even if I wanted to as the Perl is running on Linux.

    Have you any experience making use of the versions web service?

    Neil.

    ReplyDelete
  3. Neil, I haven't tried any Document Library manipulation yet. I bet you are very close to a solution!

    URLEncoded the files names?
    Changed the $SOAP->URI to "http://schemas.microsoft.com/sharepoint/soap/GetVersions"?

    ReplyDelete
  4. Hi John,

    No I'm afraid neither of those do it. I'm rather puzzled now. I've tried POSTing the XML raw at the server and got the same result.

    Will be interested to see if it works for you if you give it a go.

    Neil.

    ReplyDelete
  5. Hey Neil, I futz around with SOAP::Lite for a bit yesterday. I think I made some headway. I have been using Darren Johnstone's JavaScript APIs for SharePoint web services as a guidepost. I think I'll have to test a SOAP call via JavaScript in a CEWP, then check the call with Fiddler to double check the XML formatting.

    ReplyDelete
  6. Hi John,

    Sounds promising. Be interesting to see how that works out.

    Neil.

    ReplyDelete
  7. ---Hash out a variable to hold the full path of your document.
    ---Then do some tricks with the data hash.
    ---Finally, drop a reference to this hash in the call for GetVersions()

    $hash->{fileName} = "http://shareperl/sites/wef/Docs/Test1.doc";

    $send_this = SOAP::Data->value(
    map
    { SOAP::Data->name( $_ => $hash->{$_}) }
    keys(%{$hash})
    );

    $lists = $soap->GetVersions($send_this);


    Long story, short... I was able to get make an error free SOAP call to this service using SOAP::Lite. The data comes back as such:

    ReplyDelete
  8. Hi John,

    Excellent! Your post has got truncated though.

    The call is working for me too but with issues.

    With debug turned on I can see the XML come back and it is correct.

    Trying to use dataof gives me null.

    So close...

    Neil.

    ReplyDelete
  9. Good Post John!

    I'm hoping for an update soon, trying to pull links and actual documents and push to the browser.

    Looking into GetAttachmentCollection, but not sure this is where i need to be!

    ReplyDelete
  10. is the output from the command line,from the above code? I can't see how it generated the dynamically generated content.

    ReplyDelete
  11. The "dynamically generated" file name is created with another process not documented here.. ;D

    ReplyDelete
  12. BTW, you don't need to hack NTLM.pm (at least if using version 1.05). Instead, you can modify your code to do something like this:

    use Authen::NTLM; ntlmv2(1);
    our $sp_username = '\\wef';
    our $sp_password = 'Pa$$word16';

    Basically, tell Authen::NTLM to use ntlmv2, and don't specify an NT domain name in your username.

    ReplyDelete
    Replies
    1. I was struggling for days with an issue i had using perl to fetch an internal web page where authentication was failing on a 401. your clue in this post of not needing the NT domain name solved my issue! Thanks Karl, thanks Google, thanks to the Interweb!

      Delete
  13. I have being playing with this today.

    The "$soap->GetListCollection()" works and returns the lists, but I am having problems with the "$soap->GetListItems($in_listName, $in_viewName, $in_rowLimit);". I get the following error:
    "Value does not fall within the expected range."

    Has anyone else got this error or know what the problem is?
    (some googling suggests that something might be missing or have a wrong name in values passed to GetListItems)

    ReplyDelete
  14. I'm trying to update some information on Project Server, via spi/resource, and having issues with the first step -

    While calling CheckOutResources, passing in array of guids, I keep getting:

    System.Web.Services.Protocols.SoapException: ProjectServerError(s) LastError=GeneralParameterCannotBeNull Instructions: Pass this into PSClientError constructor to access all error information
    at Microsoft.Office.Project.Server.WebService.Resource.CheckOutResources(Guid[] array)

    Here's my code:

    my $guid = "{e5bf0d2e-c9fd-4a2b-ae79-ef2baf739975}";
    my $guid_name = name('guid', $guid);
    my $call = $soap->CheckOutResources([$guid_name]);
    die($call->faultstring()) if defined $call->fault();

    Any ideas / suggestions are appreciated.

    ReplyDelete
  15. Worked it out. Here's the working code:
    my $guid = "{e5bf0d2e-c9fd-4a2b-ae79-ef2baf739975}";
    my $guid_name = name('guid', $guid);
    my $guid_array = name('array', [$guid_name]);
    my $call = $psir_soap->CheckInResources($guid_array);
    die($call->faultstring()) if defined $call->fault();

    ReplyDelete
  16. Any idea how to get Query's to work with this??? it is all simple XML in my Javascript/SOAP implimentation - but I must admit this is all WAY over my head!

    I tried adding something like:

    my $in_query = name('query' =>

    But no luck...

    ReplyDelete
  17. Thought I'd follow-up my original comments. I got all this working successfully against Sharepoint 2007 last year.

    We're currently testing against Sharepoint 2010 and I was able to use my 2007 code without modification.

    Neil.

    ReplyDelete
  18. Since we have several users requested to integrate with Perl, we are try to follow this instruction to test it. We run into authentication issues using the code in the blog. I'm SharePoint architect but with limited experience on Perl.

    The exception is on line:
    $lists = $soap->GetListCollection();
    401 Unauthorized at E:\Share\ws\ws.pl line 27
    Client-Warning: Unsupported authentication scheme 'ntlm'.

    Here are my steps to setup.

    1. Setup ActivePerl
    2. Install SOAP:Lite through cpan command
    3. Setup SharePoint site and list and grant site collection admin to the test user account

    I also tried to add the following line:
    use Authen::NTLM; ntlmv2(1);

    But got error:
    E:\Share\ws>ws1.pl
    Can't locate Authen/NTLM.pm in @INC (@INC contains: C:/Perl64/site/lib C:/Perl64
    /lib .) at E:\Share\ws\ws1.pl line 3.

    It seems like the NTLM.pm is inside C:\Perl64\lib\LWP\Authen.

    I'm on window XP 2008 R2 64bit VM.

    Could anyone help? Please email me harryc@qualcomm.com.

    Appreciate your help in advance.

    Harry

    ReplyDelete
  19. Hey Harry,

    I guess the easy questions are: What's your farm level authentication setting? Is it Kerberos?

    Also, you may try to re-get LWP::Authen::NTLM from CPAN.

    http://search.cpan.org/~gaas/libwww-perl/lib/LWP/Authen/Ntlm.pm

    ReplyDelete
  20. I tried both NTML and Kerberos authentication sites. I got the same result 401 error. I'll try the link but nor sure how to re-get.

    Thanks.
    Harry

    ReplyDelete
  21. Hi John,
    I am also getting same kind of error as Harry has mentioned.My script is exiting on line:

    $lists = $soap->GetListCollection();

    with message
    401 Unauthorized at sharepoint.pl line 27

    I have already added the following line in my perl script:
    use LWP::Authen::NTLM ;

    As I am a newbie in sharepoint,I am not much aware of the cause of this error.Please help me to resolve this.

    Thanks in advance,
    My mail id: poojanair16@gmail.com

    Pooja Nair

    ReplyDelete
  22. I am getting the exact same issue as Pooja :(

    ReplyDelete
  23. My problem is resolved now.I am able to write on a List in Sharepoint site.

    Edit site\lib\Authen\NTLM.pm to change the following line from:

    $domain = substr($challenge, $c_info->{domain}{offset}, $c_info->{domain}{len});

    to:

    $domain = &unicode($domain);

    If you don't do that, you'll get authentication errors while running the script!

    Thanks,
    Pooja

    ReplyDelete
  24. I used a slightly modified version of this code for almost 2 years, but recently ran into a problem.
    The line:
    my $call = $soap->GetListItems($in_listName, $in_viewName, $in_rowLimit);
    returns the items I need using the "default" view of the Sharepoint list.

    The problem I ran into occurs when the "default" view returns a folder or folders instead of navigating to the end documents.

    What I need to do for robustness is send a "query" to override the default. I know
    the Sharepoint GetListItems method allows for this and that the XML I want to send looks something like:




    0




    Has anyone had to do something like this using perl SOAP::Lite and can share the code?

    ReplyDelete
  25. Guessing the empty portion of my post relates to XML tags not getting treated like text. The query I want to send is simple, something like "ID Gt 0", to bring back all list items.

    The main thing is to send a query to override the Sharepoint List default view.

    Thanks in advance for any information.

    ReplyDelete
  26. Here's a failed attempt to further illustrate what I'm trying to do. The query here was ignored and the default view was returned.

    sub lists_getitems
    {
    my $listid = shift;
    my $listName = name('listName' => $listid);
    my $in_viewName = name('viewName' => '');
    my $rowLimit = name('rowLimit' => 999999);
    my $query = name('Query' => value(
    name('Where' => value(
    name('Gt' => value(
    name('FieldRef')->attr({ 'Name' => 'ID'}),
    name('Value' => 1)->attr({ 'Type' => 'Counter'})
    ))
    ))
    ));
    my $viewFields = name('ViewFields' => value(
    name('FieldRef')->attr({ 'Name' => 'ID'}),
    name('FieldRef')->attr({ 'Name' => 'Title'})
    ));
    my $queryOptions = name('QueryOptions' => SOAP::Data->value(
    name('IncludeMandatoryColumns' => 'false'),
    name('DateInUtc' => 'true')
    ));

    my $call = $soap->GetListItems($listName, $in_viewName, $query, $viewFields, $rowLimit, $queryOptions);
    die $call->faultstring() if defined $call->fault();
    return $call->dataof('//GetListItemsResult/listitems/data/row');
    }

    ReplyDelete
  27. Hi,

    I have a perl script which populates items to a Sharepoint list.I added a new column Date in the same Sharepoint List.The date format is M/D/YYYY.When I am adding date in this format (ex: 3/1/2012) from a perl script,my perl script is failing.

    Is their any specific format in which date should be inputted to a sharepoint using perl script.
    Can someone help me on this?

    Thanks,
    Pooja

    ReplyDelete
  28. Pooja, you need the ISO date format: "yyyy-mm-dd". ex: "2012-03-01".

    SharePoint will store a time even with columns that are set to "Date Only". The default time is 00:00:00 or midnight.

    Here's how I set a "Data Only" field with PowerShell using .NET DateTime object:

    [DateTime]::Today.ToString("yyyy-MM-ddTHH:mm:ssZ")

    ReplyDelete
  29. Thanks a lot John.It is working now :)

    Thanks very much,
    Pooja

    ReplyDelete
  30. Hey guys, I am trying to figure out how to pull information continuously from a sharepoint site and have any updates announced in an IRC Channel. Anyone played with that before?

    ReplyDelete
  31. Hey Scott! I would look into PowerShell.. I've seen a few PowerShell based IRC bots in my day, and PowerShell can easily hook into the SharePoint .NET object model.

    ReplyDelete
  32. Hi - I tried out this code (thanks so much for posting by the way) and am getting a "Can't connect to MySITE:80 " error. I was unable to change the NTLM.pl script because mine did not have the domain line mentioned. Any thoughts on this?

    Thanks so much.
    Mary

    ReplyDelete
  33. Hey John,
    I am getting a 401 Unauthorized error with this script.
    Also, I don't find $domain = substr($challenge, $c_info->{domain}{offset}, $c_info->{domain}{len}); line in my NTLM.pm.

    May i am using some different version of perl. Please help me out. Thanks.

    ReplyDelete
  34. I upload 150 documents to Sharepoint every week; is there an easier way to check-in the docs? I'm trying to get a Perl script working but having authentication errors with the $lists = $soap->GetListCollection(); line.

    ReplyDelete

About Me

My photo
Chicago, Illinois, United States