MOSS 2007 : Get Last Accessed date for a site

I had a recent requirement to find out the last accessed date of the sites in the web applications on the farm. The idea was to find the sites which are rarely used and delete these sites (or archive their data).

By concept I should be able to get this data from the usage analysis data which is kept for each of the objects (Site, list, etc) On researching the involved API’s and the concept I found that the usage data which is available through Object Model has just 30 days of data. Not so good!! What if my last access date was way beyond the last thirty days?

Even if I accept this (Last 30 days of data access allowed by APIs), there is one more caveat on this. Think what would happen to the usage data if your site has been configured to be crawled by your indexer. What would the last accessed date show? You are spot on. The last accessed date property of the SPWeb is updated by the crawler. Darn .. Now how to get the data I want. I even checked the database and found that there is a last accessed date field in webs and this is the same data I got from the property I describe above.

After a bit of discussions with my peers and a lot of research on internet and internal resources I found we could use the FP RPC call on the owssvr.dll to get the usage data. This data is in a Byte Array format. I got the below link for reference but had to do a lot of plumbing to get this to work.

http://msdn.microsoft.com/en-us/library/ms478653.aspx

The method call of interest here is the GetUsageBlog method. After writing the plumbing framework to get the data from the call I was able to get the data. Again I was hit by the caveat of the Crawler updating the last accessed date. To get around this, the byte array data has the data from of accesses from browser. I found that this typically has three values – IE 4.0, IE 6.0 and other. IE 6.0 is understandable as I have windows 2003 server, but what is IE 4.0 doing there? On further research crawler is using this browser to access the site. To get the correct access date find the date for the rest of the browsers.

<Sample code>

static void Main(string[] args)
        {

            SPSite site = new SPSite("http://serverUrl");
            SPWebCollection webColl = site.AllWebs;
            foreach(SPWeb web in webColl)
            {
                UsageBlob blobOld = UsageBlob.GetUsageBlob(web.Url, false, new NetworkCredential("administrator", "Pwd", "Domain"));
                UsageBlob blobNew = UsageBlob.GetUsageBlob(web.Url, true, new NetworkCredential("administrator", "Pwd", "Domain"));
                DateTime tOld = GetAccessDate(blobOld);
                DateTime tNew = GetAccessDate(blobNew);
                int i = DateTime.Compare(tNew, tOld);
                DateTime retDate = new DateTime();
                if (i > 0)
                {
                    retDate = tNew;
                }
                else
                {
                    retDate = tOld;
                }
                Console.WriteLine("Web -- "  + web.Url +  " Last Accessed Date is:" + retDate.ToString());
                web.Dispose();
            }
            site.Dispose();
            Console.ReadLine();
        }

        public static DateTime GetAccessDate(UsageBlob blob)
        {
            UsageRecordCollection recordColl = blob.Browsers.UsageRecords;
            DateTime t = new DateTime();
            foreach (UsageRecord rec in recordColl)
            {
                if (!(rec.Key.ToUpper().Contains("INTERNET EXPLORER  4.01") || rec.Key.ToUpper().Contains("[OTHER]")))
                {
                    if (t == null)
                    {
                        t = rec.LastAccessDate;
                    }
                    else
                    {
                        int i = DateTime.Compare(t, rec.LastAccessDate);
                        if (i < 0)
                        {
                            t = rec.LastAccessDate;
                        }
                    }
                }
            }
            return t;
        }

</Sample code>

This code would also require the plumbing class library code. I am attaching the plumbing code I have used so this can help you avoid the time and research expended by me :)

UsageBlobParser.zip