Sue Hernandez's SharePoint Blog

SharePoint and Related Stuff

T-SQL query to get most recent 1 entry for each user

Hi, usually I write about SharePoint.  This week, I was attempting to get a list of all users on all the web applications on our Farm for the last 365 days.  Luckily one of our team members was already parsing through IIS Log files and writing the entries to a custom DB to be processed later.  These entries were written to the db basically exactly as they appear in the IIS Log – 1 entry for each and every page hit (I think he might have stripped out everything except .aspx pages).

So there’s currently 41,800,000 + rows in this custom DB, and really it’s still in a testing phase, because no indexes have yet been created on it (the concept is still being tested).  So I needed a way to say “Give me the Login Name and the URL of every unique user in the last 365 days, and the URL should be the most recent one from that unique Login Name, as long as it’s within 365 days”.

I haven’t used SQL in a while, and honestly I’m not a T-SQL guru, so I began to write a cursor to do this.  Yea.   Well 45 minutes later, when the query was still running and I had barely gotten a handful of anything, I thought there must be a better way.

I won’t go into the full explanations, because I don’t know if I’d be explaining it correctly, but here’s what I ended up with:

DECLARE @fromDate datetime
SET @fromDate = '5/5/2014'
SELECT RowNum, UserName, FullPageUrl, LastLogEntry
FROM (
   SELECT ROW_NUMBER() OVER(PARTITION BY logs.UserName ORDER BY logs.LogDateTime DESC) AS RowNum,
      logs.UserName, wa.Url + logs.Uri AS FullPageUrl, logs.LogDateTime AS LastLogEntry
   FROM IISLogs logs
      INNER JOIN WebApps wa ON logs.ApplicationId = wa.Id
) _
WHERE RowNum = 1
  AND LastLogEntry >= @fromDate
ORDER BY LastLogEntry ASC   -- or you could order by UserName or FullPageUrl

 

Basically this is saying Group By UserName, and only give me the first one, but take the first one in order of the Log Date in descending order.

This query took just over 5 minutes.  Also I have no idea why but the _ right before the last where clause absolutely has to be there.  I will look that up some other time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: