02/04/2013 Leave a comment
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