Getting Last Accessed user for a SharePoint Site

Assume that you would like to get last accessed users for a specific site or web in SharePoint. The correct approch is creating a custom IIS Module to get update statistics by every request. If you dont want to do that there you may alternatively use following method.

But we have some conditions:
1) Usage and Health Data Collection Service Application is running.
2) And Microsoft SharePoint Foundation Usage Data Import job running in a small interval (one hour maybe). (Because the data first stores in a Usage file in file System and when the timer job has been executed that data transfered to you Logging Database) Thats mean it is not real time. (If you would like a statistics in real time i suggest that use a custom IIS module)

Than you can search the last 5 accessed user from SQL Server Management Studio by quering Logging Database Server (It would be delayed by interval of Import Timer Job)

Declare @serUrl as nvarchar(MAX), @sitUrl as nvarchar(MAX) , @wUrl as nvarchar(MAX)
Declare webcursor CURSOR FOR select serverurl,siteurl,weburl from RequestUsage group by ServerUrl,SiteUrl,WebUrl
Open webcursor

FETCH NEXT FROM webcursor INTO @serUrl,@sitUrl,@wUrl
WHILE @@FETCH_STATUS = 0 
BEGIN
PRint 'ServerUrl: ' + @serUrl + ' Site Url:' + @sitUrl +  ' Web Url:' + @wUrl
select Top 5 LogTime, UserLogin,ServerUrl,SiteUrl,WebUrl from RequestUsage 
where ServerUrl = @serUrl and SiteUrl = @sitUrl and WebUrl =  @wUrl
--Dont Forget to Exclude SharePoint\system , Central Administration Web Site, Crawler Account, And Timer Service Account for correct results.
and ServerUrl <> 'http://contoso.com:32843'  and UserLogin <> 'SHAREPOINT\system' and UserLogin<> 'Contoso\Crawler'
order by LogTime desc 
FETCH NEXT FROM webcursor INTO @serUrl,@sitUrl,@wUrl
END
CLOSE webcursor
DEALLOCATE webcursor
Advertisements