Viewtracker Reports with SQL apps

Notes:

We do not offer support for custom-configured Space Usage Reports.
This documentation page is not maintained. We advise using the built-in Viewtracker reports or the Viewtracker API.

Space Usage Report with SQL for Confluence

The Viewtracker app can be used with conventional SQL apps like SQL for Confluence. In order to use the app, a data source has to be declared. This is described in the following article: Configure a Datasource.

“SQL for Confluence” is an Appfire app. If you have questions, please contact the Appfire support.

Enable Custom Space Reports

To enable the Custom Space Reports, you need to activate them in the Viewtracker administration menu.

Examples

The following queries might need some adjustments because of different database settings or SQL dialects.

Viewtracker Space Usage Report, SQL version

CODE

h2. Most viewed pages
 
{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{sql:dataSource=example}
select title as "Page", totalViews as "Total Views", totalVisitors as "Total Visitors"
from(
 
select content_id2, sum(count) as totalViews, count(user_key) as totalVisitors
from (
select * from(
select content_id as content_id2, count(user_key) as count, user_key
from AO_05769A_VISIT_ENTITY
group by content_id, user_key) as viewed, (
select distinct `CONTENT_ID`, `SPACE_KEY`
from AO_92296B_AORECENTLY_VIEWED
)as key_
where `CONTENT_ID`=content_id2
) as withKey
where SPACE_KEY like "@space"
group by content_id2
order by totalViews desc) as all_
 
left join CONTENT on CONTENT.`CONTENTID`=content_id2
LIMIT 10;
 
 
 
{sql}
{chart}
 
\\
 
h2. Most active visitors
{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{sql:dataSource=example}
 
select user_mapping.username as "Username", totalViews as "Total Views", totalPagesViewed as "Total Pages Viewed"
from(
 
select user_key as user_, sum(count) as totalViews, count(user_key) as totalPagesViewed
from(
select * from(
select user_key, count(`CONTENT_ID`) as count
from AO_05769A_VISIT_ENTITY
group by user_key, `CONTENT_ID`) as viewed, (
select distinct `USER_KEY` as key2, `SPACE_KEY`
from AO_92296B_AORECENTLY_VIEWED)as key_
where user_key=key2
) as withKey
where SPACE_KEY like "@space"
group by user_key
order by totalViews desc) as all_
 
left join user_mapping on user_mapping.`user_key`=user_
LIMIT 10;
{sql}
{chart}

View count per User and Page

Get view count for each page, user and the space name.

SPACE NAME	TITLE	Username	Count
TQM	Project Documents	Erika	18
TestSpace	Test Page	Martha	9
TQM	Letter Templates	Heidy	8
TQM	Project Documents	Reto	6
TQM	Project Documents	Urs	5

View count per User and Page

SQL

{sql:dataSource=example}
select Spacename as "Space name", title, user_mapping.username as Username, count as Count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_

left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
/* where space_ like "SPACEKEY"  ## In case the report should only cover one Space*/
order by count desc
{sql}

View count per User

Get the views of a user in one space.

username	count
Erika	31
Martha	19
Heidy	15
Reto	13
Urs	12

View count per User

SQL

{sql:dataSource=example}
select Spacename as "Space name", title, user_mapping.username as Username, count as Count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_
left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
where space_ like "TQM"
order by count desc
{sql}

View count per Page

Get a list of the most viewed pages.

SPACE NAME	TITLE	count
TQM	Project Documents	63
TQM	Letter Templates	28
TestSpace	Test Page	12
TQM	Project	11
TQM	Administration	5
TQM	Contracts	5

View count per Page

SQL

{sql:dataSource=example}
select Spacename, title, sum(count) as count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_

left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
/* where space_ like "SPACEKEY"  ## In case the report should only cover one Space*/
group by spacename, title
order by count desc
{sql}

View count per content label

SQL

SELECT title, SPACES.SPACEKEY , user_mapping.username , count(*) as views 
FROM AO_05769A_VISIT_ENTITY AS Views
LEFT JOIN CONTENT ON CONTENT.CONTENTID = Views.CONTENT_ID
LEFT JOIN CONTENT_LABEL ON CONTENT_LABEL.CONTENTID = CONTENT.CONTENTID
LEFT JOIN LABEL ON LABEL.LABELID = CONTENT_LABEL.LABELID
LEFT JOIN SPACES ON SPACES.SPACEID = CONTENT.SPACEID
LEFT JOIN user_mapping ON user_mapping.user_key = Views.USER_KEY
WHERE LABEL.NAME = 'riada' # define the label here
GROUP BY title, LABEL.NAME, SPACES.SPACEKEY  , user_mapping.username

Counts per Hour

MySQL

SQL

select extract(hour from VISIT_TIME) as theHour,
         count(*) as numberOfItems
  from AO_05769A_VISIT_ENTITY
 group by extract(hour from VISIT_TIME)