Quick and Dirty Scripting

A blog that focuses on automating system administration tasks for Linux, Windows, and VMware ESX

Saturday, November 7, 2009

 

SharePoint Trending with Google APIs

At my day job, I manager a fairly large SharePoint deployment. We currently have about 450GB of content spread across 6-7 content databases. Needlessly to say, because SharePoint stores all of its information inside SQL Server database and Microsoft’s recommendation for no larger than 100GB content databases, trending our growth is very important to our team. For a while, we have been taking snapshots of the content database’s size, storing the information in Excel and generating Pivot table and charts. This worked fine but I wanted better. I’ve always liked the way Google’s Finance pages looked so I was excited to learn that you can use those same APIs in your own site. I think I’ve come up with a pretty neat solution by utilizing the SharePoint object model ,PowerShell, a little jQuery, Google’s APIs and an awesome course from EndUserSharepoint (Url - http://www.endusersharepoint.com, Twitter - @ESUP) . I’m always open for suggestions or improvements so after you read my posting, please let me know what you think.

Enough chit chat. Let’s get into how this works. To get this working for you, you’ll need three things:

  1. A SharePoint List to store the size of the SharePoint databases
  2. A PowerShell script to populate the list on a nightly basis
  3. A Web Part page with a Content Editor Web part + jQuery and the SharePoint List to generate the graph

SharePoint List

This easiest thing to create. Its just a custom SharePoint list with the following columns.

  • Web Application – the name of the SharePoint web application. I typically rename the default Name field.
  • Database Name – the name of the of the Content Database.
  • Database Size – a number field that will store the size of the database. The one thing that I do not like about the number field is that it inserts commas into the number. I debated about making this a string field but I decided to just handle this with a little jQuery
  • Date – this will hold the date the database snapshot was taken. I give it a default value of today’s date and only select date, not date/time.

A couple other things to note about the list:

  1. You will need to grant your SharePoint farm account contribute access to the list. This is because the PowerShell script will run as the Farm account. In a locked down environment like mine the only account that can access the databases is the Farm account.
  2. To keep the list from growing too large, I setup an IRM policy to delete a list item 90 days after the item is created. This keeps my list nice and trim automatically.

PowerShell Script

The PowerShell script utilizes the SharePoint object model so it has to run on one of the SharePoint servers in your farm. I choose my Central Admin server but it can be any server in the farm. The idea of the script to is to loop through every Web Application and gather the size of each Content Database associated with it and then upload the information to the SharePoint list. Its pretty straight forward except for one issue with SharePoint Content Database names when working with PowerShell. This discussion thread goes into good detail on how to get around it - http://groups.google.com/group/microsoft.public.windows.powershell/browse_thread/thread/16c06d92b2385325.

   1: . ..\SharePointFunctions.ps1
   2:  
   3: #Powershell had an issue with the SPContentDatabase assembly function Name and DiskSizeRequired. This is a work around
   4: #See http://groups.google.com/group/microsoft.public.windows.powershell/browse_thread/thread/16c06d92b2385325 for more information 
   5: $nameMethod = [Microsoft.Sharepoint.Administration.SPContentDatabase].getMethod("get_Name")
   6: $diskMethod = [Microsoft.Sharepoint.Administration.SPContentDatabase].getMethod("get_DiskSizeRequired")
   7:  
   8: set-variable -option constant -name TrendingList -value "Trending-SPDatabase"
   9: set-variable -option constant -name TrendingSite  -value "http://tis-collaboration.jpmchase.net/ti/shareddotnet"
  10: set-variable -option constant -name WebApplication  -value "Web Application" 
  11: set-variable -option constant -name DatabaseName -value "Database Name"
  12: set-variable -option constant -name DatabaseSize  -value "Database Size (MB)"
  13:  
  14: $MB = 1024*1024
  15:  
  16: #Function main - Where all of the fun is
  17: function main() {
  18:  
  19:     #Loop through all WebApplications
  20:     get-SPWebApplications -name * | % {
  21:         
  22:         #Don't need to display information on the Central Admin site
  23:         if( -not $_.IsAdministrationWebApplication ) {
  24:                         
  25:             $strWebApp = $_.Name.TrimStart()
  26:                     
  27:             #Display information about the content database attached to this WebApp
  28:             $ContentDatabaseCollection = $_.ContentDatabases
  29:             
  30:             $ContentDatabaseCollection | % {
  31:     
  32:                 $newlistitem  = @{}
  33:                 $newlistitem[$WebApplication] = $strWebApp
  34:                 $newlistitem[$DatabaseName] = $nameMethod.Invoke($_, "instance,public", $null, $null, $null)
  35:                 $newlistitem[$DatabaseSize] = [math]::round( ($diskMethod.Invoke($_, "instance,public", $null, $null, $null) / $MB), 0)
  36:                 
  37:                 add-toSpList -url $TrendingSite -list $TrendingList -entry $newlistitem 
  38:  
  39:             }
  40:         }
  41:     }
  42:  
  43: }
  44: main

The variables TrendingSite, TrendingList, WebApplication, DatabaseName, DatabaseSize will need to be updated for your environment.


The function get-SPWebApplication will return a list Web Application objects. You can limit the list to a specific Web Application by replacing “–name *” with “–name $name_of_web_application”



   1: function get-SPWebApplication( [string] $name )
   2: {
   3:     $WebServiceCollection = new-object microsoft.sharepoint.administration.SpWebServiceCollection( get-SPFarm )
   4:     $WebServiceCollection | % { $WebApplications += $_.WebApplications }
   5:     
   6:     return ( $webApplications | where { $_.Name.ToLower() -like "*"+$name.ToLower()+"*" } | select -Unique )
   7: }
   8:  
   9: function get-SPFarm()
  10: {
  11:     return [microsoft.sharepoint.administration.spfarm]::local
  12: }

To add the information to the SharePoint List, we have to pass a hash table with name/value pairs to the function add-toSpList. We also have to pass the URL of the Site that hosts the list and the List name.



   1:  
   2: function add-toSpList ( [Object] $url, [string] $list, [HashTable] $entry)
   3: {
   4:     if( $url.GetType().Name -eq "String")
   5:     {
   6:         $site = new-object Microsoft.SharePoint.SPSite($url)
   7:         $web = $site.OpenWeb()
   8:     }else
   9:     {
  10:         $web = $url
  11:     }
  12:     
  13:     $splist = $web.Lists[$list]
  14:     $newitem = $splist.items.Add() 
  15:  
  16:     $entry.Keys.GetEnumerator() | % {
  17:         $newitem[$_] = $entry[$_]
  18:     }
  19:     
  20:     $newitem.update() 
  21:     $web.Dispose()
  22:     $site.Dispose() 
  23: }

Now that we have to entire script, all you have to do is schedule it to run as often as you wish via the Windows Scheduler. Make sure you run the script as the SharePoint Farm Admin.


SharePoint WebPart Page + jQuery


Now the fun begins. I owe a ton of thanks to the EndUserSharepoint.com team as I used a lot of their work as a basis for what I’m trying to do. First, what you need to do is add a Web Part page to your site. On this page, add a Content Editor Web Part and the SharePoint List that you created earlier. For the list, I filtered the view to only display one database name. I also only selected he database name, size, and date columns. If I have more than one database (or web application) then I will need to create a page for each. The view should look something like this:






Next you have to get jQuery working with your site, and this means adding code to the Content Editor Web Part. I would suggest heading over to EndUserSharepoint.com (http://www.endusersharepoint.com/?s=jquery+for+everyone) for more information on jQuery, but basically jQuery is a collection of JavaScript functions and objects that make JavaScript coding a lot easier. You need to load the jQuery engine and thankfully Google hosts it for you. All you need to do is link to their URL in your code. This is what I done for this to work. Google also hosts the visualization APIs that we need to generate the graph. If you want to learn more about the available APIs and samples on how to use them, head over to http://code.google.com/apis/visualization/


The code for the Content Editor Web Part + jQuery is as follows: (PS: This has to be added via the CEWP’s source button, not the Rich Text Editor.)



   1: <script type="text/javascript" src="https://www.google.com/jsapi"></script>
   2:  
   3: <script type="text/javascript">
   4:     google.load('visualization', '1', {packages: ['annotatedtimeline']});
   5:     function drawVisualization() {
   6:       var dataList = $("td.ms-vb2 div");
   7:       var dateList = $("td.ms-vb2 nobr");
   8:       var dateArray = new Array();
   9:  
  10:       $.each(dateList, function(i,e)
  11:       {
  12:         var y = $(e).text();
  13:         dateArray[i] = y;
  14:       });
  15:  
  16:       var data = new google.visualization.DataTable();
  17:       data.addColumn('date', 'Date');
  18:       data.addColumn('number', 'Size');
  19:       data.addRows(dataList.length);
  20:       
  21:       $.each(dataList, function(i,e)
  22:       {
  23:          var x= $(e).text().replace(",","");
  24:          data.setValue(i, 0, new Date(dateArray[i]) );
  25:          data.setValue(i, 1, parseInt(x * 1048576) );
  26:       });
  27:       
  28:       var annotatedtimeline = new google.visualization.AnnotatedTimeLine(document.getElementById('trending'));
  29:       annotatedtimeline.draw(data, {'displayAnnotations': true});
  30:     }
  31:     
  32:     google.setOnLoadCallback(drawVisualization);
  33:  
  34: </script>
  35: <div id="trending" style="width: 100%; height: 400px;"></div>

Well there you go. Once you have everything in place you should have a page the looks similar to this:

















I hope that everyone has enjoyed this post and I always look forward to any feedback.

posted by Brian Denicola  # 6:38 PM
Comments:
Hi Brian,

Great post! I really liked the way you combined technologies provided by different organizations. It is a great example of focusing more on the business needs than on the technology itself.

Mauricio
# posted by Anonymous Mauricio : November 8, 2009 7:33 PM
 
Nice post Brian
# posted by Anonymous Marshal : November 10, 2009 4:19 AM
 
I don't understand how the content editor web part hooks the list. What is the list name? Where does that exist in the code?
# posted by Blogger Doug : November 24, 2009 1:56 PM
 
Doug - Good question. Since jQuery is really just javascript, it doesn't know anything about SharePoint or Lists or anything like that. It just knows basic HTML. A SharePoint list is rendered by ASP.NET/IIS as a HTML table. I just use the built in jQuery filters to grab all of the data from the table. The trick is knowing how to pick the table columns. SharePoint doesn't make this easy in 2007... Hopefully 2010 will be easier.

Anyways, jQuery gets the data for the chart from the two lines

var dataList = $("td.ms-vb2 div");
var dateList = $("td.ms-vb2 nobr");
# posted by Blogger Brian Denicola : November 25, 2009 11:10 AM
 
Thanks for the post and the response... I am receiving a script error on the beginning of the first of these two lines - object expected. I had a table with a text column for the number and changed it to numeric and now see the HTML table with div and nobr...
# posted by Blogger Doug : November 30, 2009 9:03 AM
 
Doug - Send me a screen shot of your error on Twitter @bjd145 and I will see what the issue is.
# posted by Blogger Brian Denicola : November 30, 2009 8:37 PM
 
Was a solution found for the error Doug was getting. I have that same error.
# posted by Blogger Curtis Lupton : December 9, 2009 2:39 PM
 
Doug, Curtis - I believe I know what the issue is with your code. In the blog, I mentioned that you should head to endusersharepoint.com to show you how to load the jQuery engine. There are many ways of loading the engine and EndUserSharePoint.com does a good job of explaining the different options so I didn't want to repeat what they have already done. But I can understand the confusion. The simplest way is to add another line to your CEWP that calls the jQuery Engine from Google. It will be something like this:

script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"

Or you can use the SmartTools features - http://smarttools.codeplex.com/. This will add jQuery to any SharePoint page and this is what I do.

Hope this helps

Brian
# posted by Blogger Brian Denicola : December 9, 2009 3:35 PM
 
Had the same error, to solve, ass the following line on top of your script in the CEWP:
type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js"

rem: Enclose with Script tag at the beginning and the end
# posted by Blogger roland : December 13, 2009 11:28 PM
 
Post a Comment





<< Home

Archives

October 2007   November 2007   December 2007   January 2008   February 2008   April 2008   October 2009   November 2009  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]