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:
- A SharePoint List to store the size of the SharePoint databases
- A PowerShell script to populate the list on a nightly basis
- 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:
- 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.
- 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.
