Saturday, June 2, 2012

Site Traffic Hits Report on Every Item on a SharePoint Site

Requirement: Get the site traffic hits count for the past 30 days on each and every File/List/Page/etc stored on a MOSS 2007 site.

C# Object Model code to Generate SharePoint hits report:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Data;
using System.Diagnostics;
using System.IO;

namespace SharePoint.AdminReport
{
    class GetSiteUsageReport
    {
        //Method to Convert Data Table to CSV
        public static string ToCSV(DataTable table, string webURL)//, string listURL) 
        { 
            var result = new StringBuilder();

              foreach (DataRow dr in table.Rows)
              {
                  result.Append(webURL + "\t" + (dr["Folder"].ToString() == "" ? "" : "/") + dr["Folder"].ToString() + "/" + dr["Page"].ToString() + "\t" + dr["Total Hits"].ToString() + "\t" + dr["Most Recent Day"].ToString() + "\n");
              }
            return result.ToString(); 
        } 

        //Method to Get Usage Data
        public static DataTable GetWebUsageData(SPWeb oSPWeb)
        {
            try
            {
                //DataTable for Hits result - Because GetUsageData returns DataTable!
                DataTable dtHits = new DataTable();

                dtHits = oSPWeb.GetUsageData(SPUsageReportType.url, SPUsagePeriodType.lastMonth);
                if (dtHits != null)
                {
                    return (dtHits);
                }
                else
                {
                    return (null);
                }
            }
            catch (Exception Ex1)
            {
                return (null);
            }
        }


        static void Main(string[] args)
        {
            string site;

            try
            {
                if (args.Length == 0)
                {
                    Console.WriteLine("Enter the Site Collection URL:");
                    site = Console.ReadLine();
                }
                else
                {
                    site = args[0];
                }

                SPSite tmpSite = new SPSite(site);
                //objects for the CSV file generation
                StreamWriter sw = new StreamWriter("c:\\SiteUsageReport.csv",false); 

                //Write the CSV Header
                sw.Write("Site \t Page \t Total Hits \t Most Recent Day \n");
                    
                   //Enumerate through each sub-site
                    foreach (SPWeb tmpWeb in tmpSite.AllWebs)
                    {
                            //DataTable for Hits set - Because GetUsageData returns DataTable!
                            DataTable dtHits = new DataTable();
                            dtHits = GetWebUsageData(tmpWeb);

                            if (dtHits != null)
                            {

                            string result = ToCSV(dtHits, tmpWeb.Url);

                                     sw.Write(result);
                            }
                    }
                //Dispose of the Root Site Object
                sw.Close();
                tmpSite.Dispose();
            }

            catch (Exception ex)
            {
                System.Diagnostics.EventLog.WriteEntry("Site Usage Report", ex.Message);
            }
               
        }
    }
}

and the Output:
sharepoint site usage report total hits

 With Pivot Analysis:
sharepoint site hits report

PowerShell version:
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
 
#Functions to Imitate SharePoint 2010 Cmdlets in MOSS 2007
function global:Get-SPWeb($url) 
{
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null) 
        {
              $web=$site.OpenWeb();       
        }
    return $web
}

 
        #Method to Get Usage Data
        Function GetWebUsageData($Web)
        {
            try
            {
                #DataTable for Hits result - Because GetUsageData returns DataTable!
                $dtHits = New-Object System.Data.DataTable  
 
                $dtHits = $Web.GetUsageData("url", "lastMonth") 

                if ($dtHits -ne $null)
                {
                    return ($dtHits);
                }
                else
                {
                    return ($null);
                }
            }
            catch 
            {
                Write-Host $_.Exception.Message -ForegroundColor Red
                return ($null);
            }
        }
 
    $WebURL = "http://sharepoint.crescent.com/operations/sfdc/"
    
    $CurrentPath = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
    $OutPutFile = Join-path $CurrentPath "SiteUsageReport.txt"
    
    #Get the Web
    $Web = Get-SPWeb $WebURL
 

    #create a CSV file
    "Page `t Total Hits `t Last Accessed" > $OutPutFile #Write the Headers in to a text file

    #Get the Hits Data
    $HitsDT = GetWebUsageData($Web)
    if ($HitsDT -ne $null)
    { 
        foreach($dr in $HitsDT)
        {
          
          $result = $webURL +"/" + $dr["Folder"]+"/"+$dr["Page"] + "`t" + $dr["Total Hits"] + "`t" + $dr["Most Recent Day"]
          $result >> $OutPutFile  #append the data
        }
    }



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

4 comments :

  1. Hello Rajack,
    I tried the PS version in my 2013 SP environment, and it is returning null to me. Is this solution only works for 2007 SP? Everything (Usage reporting) seem to be correctly configured in my environment, but still the script is not returning any thing.
    Any help would be appreciated.

    Thanks,
    Soni

    ReplyDelete
    Replies
    1. GetUsageData returns NULL when Site is locked/quota exceeded. Make sure Usage data collection is in place. If Usage Data collection is enabled, Check these timer jobs:

      Central Administration >> Monitoring >> Review Job definitions under Timer Jobs. Make sure that the timer jobs are enabled:

      Microsoft SharePoint Foundation Usage Data Import
      Microsoft SharePoint Foundation Usage Data Processing

      Run them at least once. Sometimes it takes a few hours for the data to populate.

      Delete
    2. Thank You Rajack for replying. We do have these 2 jobs enabled and can see all the data on site. I mean go to site setting and for individual pages too I can see popularity trends for each individual page. Through PowerShell I'm unable to see any result. Any help?

      Thanks,
      Soni

      Delete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...