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
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
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
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
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]
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
Admin | CMS Configuration | Sites and then edit the Advanced Settings on the appropriate site.