Add an Access Log to Any Rock Page

  • By Jim Michael 2 Years Ago
Updated on 2018-08-23 to include SQL necessary for v7 and newer Rock instances.

We have several pages in our Rock internal site (and our public site) that we needed to monitor for access... kind of a poor man's web log analyzer. One example of this is our Check-in Portal that volunteers use on the weekend to register new families for child check-in. In the spirit of "Trust, but Verify" we needed an easy way to see who has accessed that portal and when, as a way to (hopefully not) spot any abuse of the system and there are several other pages we're logging for different reasons too. The solution we use leverages some SQL, the PageViews table, and a Dynamic Data block.

An Example of the Log

Access_Log.png

How To

1. Create a page where your log will live, or go to an existing appropriate page. Right Sidebar or Full Width layouts both work well for this, so use what you prefer

2. Add a Dynamic Data block to the page

3. Edit the Dynamic Data block's Criteria and add the following SQL to the block

SELECT distinct
   V.[DateTimeViewed] AS [Date]
 , <a href="https://rock.yourchurch.org/Person/' + CAST(P.[Id] AS VARCHAR) + '">' P.[NickName] + ' ' + P.[LastName] + '</a>;' AS [Viewed By]
 , CASE WHEN S.[IpAddress] LIKE '10.%' THEN 'On-Site' ELSE 'Off-Site (' + S.[IpAddress] + ')' END AS [Location]
 , ua.OperatingSystem OS
 , ua.Browser
FROM [PageView] V
INNER JOIN [PageViewSession] S ON S.[Id] = V.[PageViewSessionId]
INNER JOIN [PersonAlias] PA ON PA.[Id] = V.[PersonAliasId]
INNER JOIN [Person] P ON P.[Id] = PA.[PersonId] Inner join PageViewUserAgent ua on ua.id = s.PageViewUserAgentId
WHERE Url = 'https://rock.yourchurch.org/CheckinCentral'
ORDER BY V.DateTimeViewed DESC
Note that in v7 the PageViews table was removed and these now log to the Interactions tables. Consequently, for v7 and newer you need to use this SQL instead:
SELECT distinct
   I.[InteractionDateTime] AS [Date]
 , '<a href="https://rock.yourchurch.org/Person/' + CAST(P.[Id] AS VARCHAR) + '">' + P.[NickName] + ' ' + P.[LastName] + '</a>' AS [Viewed By]
 , CASE WHEN S.[IpAddress] LIKE '10.%' THEN 'On-Site (' + S.[IpAddress] + ')' ELSE 'Off-Site (' + S.[IpAddress] + ')' END AS [Location]
 , ua.OperatingSystem
 , ua.Application
FROM [Interaction] I
INNER JOIN [InteractionSession] S ON S.[Id] = I.[InteractionSessionId]
INNER JOIN [PersonAlias] PA ON PA.[Id] = I.[PersonAliasId]
INNER JOIN [Person] P ON P.[Id] = PA.[PersonId] 
INNER JOIN [InteractionDeviceType] ua on ua.[Id] = s.[DeviceTypeId]
WHERE I.InteractionData = 'https://rock.yourchurch.org/CheckinCentral' AND I.Operation = 'View'
Order by I.InteractionDateTime DESC

There are a few things to edit before saving the block:

1. In the first href tag, replace the https://rock.yourchurch.org portion of the URL with the hostname of your own Rock server. This forms the link to the person's detail page.

2. Replace the WHERE Url =URL (or WHERE I.InteractionsData = for v7+) with the URL to the page you want to monitor, again using your own Rock server hostname. In our example it's https://rock.yourchurch.org/CheckinCentral, but change this to whatever Rock server/page you're trying to log.

3. For our logging, we wanted to note whether the person viewing the page was "On site" or "Off site" (we host Rock on a local server.) We accomplished this by looking at part of the IP address the client used to access the page. Since we use 10.x.x.x IP addresses at all of our campuses, we used LIKE 10.% in the CASE statement of the SQL query, which basically says "consider any IP address that starts with 10. "On Site", otherwise consider it "Off site." Change this LIKE condition to match whatever IP scheme makes sense for your network. Alternatively, if you don't care about the distinction between on/off site. just remove the entire CASE WHEN line from the query.

Note that if you're cloud hosting Rock, this idea is less practical, but you could accomplish a similar result by looking for the public IP of your network that Rock clients use to get to the Internet instead of your private IP scheme. Contact your IT Manger if none of that makes sense ;-)

4. You shouldn't need to change anything else in the Dynamic Data block to make this work, but make any changes you wish and save the block. You should now see an access log similar to the one shown above.

Note: Since this query is looking at the PageViews/Interactions tables for its data, you will only get a log as far back as you've told the site to log page views (and if you get NO data back, it's likely you're not logging page views at all for that site!) You can control how long page views are kept around on a per-site basis via Admin | CMS Configuration | Sites and then edit the Advanced Settings on the appropriate site.


@jimmichael
The Crossing
St. Louis, MO

IT Manager at The Crossing in St. Louis. Lover of all things Rock!