Weave statistic can be sent to a database for further analyses. The bundle can be downloaded from here (2017-02-15)

Configuration

Weave statistics config

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns="urn:com.cohga.server.config#1.0" xmlns:statistics="urn:com.cohga.server.statistics.db#1.0">

	<statistics:config>
		<!-- datasource is required -->
		<!-- The name of the data source that the records will be written to -->
		<datasource>storage</datasource>

		<!-- table is optional and will default to wv_statistics -->
		<!-- This is the table where the records will be written -->
		<!-- Weave will attempt to create the table if it doesn't exist -->
		<table>wv_statistics</table>

		<!-- maximumDelayInSeconds is optional and will default to 60 seconds -->
		<!-- The maximum time to wait before writing any pending records -->
		<maximumDelayInSeconds>60</maximumDelayInSeconds>

		<!-- count is optional and will default to 30 -->
		<!-- The minimum number of records that need to be available before they are written -->
		<count>30</count>
	</statistics:config>

</config>

Configuration for ikartan.se

The table is managed together with the ikartan admin database in schema 'weave'

Weave statistics config ikartan

create schema weave;
-- Table: weave.wv_statistics
-- DROP TABLE weave.wv_statistics;
CREATE TABLE weave.wv_statistics
(
  id serial primary key,
  submitted timestamp without time zone NOT NULL,
  duration integer NOT NULL,
  userid character varying(50) NOT NULL,
  ip character varying(50) NOT NULL,
  request character varying(200) NOT NULL
);

Configuration is located in config_ikartan/config_ikartan_common.xml

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns="urn:com.cohga.server.config#1.0" xmlns:statistics="urn:com.cohga.server.statistics.db#1.0">

....


	<statistics:config>
		<!-- datasource is required -->
		<!-- The name of the data source that the records will be written to -->
		<datasource>ds.users</datasource>

		<!-- table is optional and will default to wv_statistics -->
		<!-- This is the table where the records will be written -->
		<!-- Weave will attempt to create the table if it doesn't exist -->
		<table>weave.wv_statistics</table>

		<!-- maximumDelayInSeconds is optional and will default to 60 seconds -->
		<!-- The maximum time to wait before writing any pending records -->
		<maximumDelayInSeconds>60</maximumDelayInSeconds>

		<!-- count is optional and will default to 30 -->
		<!-- The minimum number of records that need to be available before they are written -->
		<count>30</count>
	</statistics:config>
...


</config>

Example of outputs

All

SELECT * FROM weave.wv_statistics;

Totals

SELECT sum((duration+500)/1000) as seconds, count(*) FROM weave.wv_statistics;

Totals this week

SELECT sum((duration+500)/1000) as seconds, count(*) FROM weave.wv_statistics
where submitted > date_trunc('week', current_date);

Totals per user

SELECT userid, sum((duration+500)/1000) as seconds, count(*) FROM weave.wv_statistics group by 1;

Statistics per weekday

SELECT
CASE date_part('isodow',submitted) WHEN 1 THEN 'Måndag' WHEN 2 THEN 'Tisdag' WHEN 3 THEN 'Onsdag' WHEN 4 THEN 'Torsdag' WHEN 5 THEN 'Fredag' WHEN 6 THEN 'Lördag' WHEN 7 THEN 'Söndag' END as weekday,
count(distinct userid) as users,
sum((duration+500)/1000) as seconds,
count(*) as requests
FROM weave.wv_statistics
group by date_part('isodow',submitted)::int, 1
order by date_part('isodow',submitted)::int;

Statistics per hour

SELECT
h.a as hour,
count(distinct userid) as users,
sum((duration+500)/1000) as seconds,
count(*)-1 as requests
FROM weave.wv_statistics
FULL JOIN (select unnest((array[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23])) as a) h ON h.a = date_part('hour',submitted)
group by 1
order by 1

Join with other tables

Customer

SELECT customers.name, sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
where request = 'com.cohga.ExecuteReport'
group by 1 order by 3 desc;

Customer and day

SELECT customers.name, date(submitted), sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
group by 1,2 order by 2,1;

Customer and user name per day

SELECT customers.name, (users.firstname || ' ' || users.lastname) as name, date_part('day',submitted), sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
group by 1,2,3 order by 3,1;

Pivot table with column week and hours ( 0- 239) showing server usage in seconds

SELECT * FROM crosstab(
$$SELECT
date_part('week',submitted),
h.a as hour,
sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
FULL JOIN (select unnest((array[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23])) as a) h ON h.a = date_part('hour',submitted)
where date_part('week',submitted) > 0
group by 1,2
order by 1$$,
$$ SELECT m FROM generate_series(0,23) m $$
) as ( week int, "0" int, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int, "10" int, "11" int, "12"
int, "13" int, "14" int, "15" int, "16" int, "17" int, "18" int, "19" int, "20" int, "21" int, "22" int, "23" int);

Pivot table with column week and weekdays showing server usage in seconds

SELECT * FROM crosstab(
$$SELECT
date_part('week',submitted)::int,
date_part('isodow',submitted)::int as weekday,
sum((duration+500)/1000) as seconds
FROM weave.wv_statistics
group by 1,2
order by 1$$,
$$ SELECT m FROM generate_series(1,7) m $$
) as ( week int, "Måndag" int, "Tisdag" int, "Onsdag" int, "Torsdag" int, "Fredag" int, "Lördag" int, "Söndag" int );

List with categorised requests

SELECT
--date(submitted),
case
when lower(request) like '%entity%' then 'Edit'
when lower(request) like '%executedata%' then 'Data'
when lower(request) like '%edit%' then 'Edit'
when lower(request) like '%dms%' then 'Dokument'
when lower(request) like '%select%' then 'Urval'
when lower(request) like '%index%' then 'Snabbsök'
when lower(request) like '%search%' then 'Sökningar'
when lower(request) like '%admin%' then 'Admin'
when lower(request) like '%report%' then 'Rapporter'
when lower(request) like '%list%' then 'Listor'
when lower(request) like '%map%' then 'Kartor'
when lower(request) like '%export%' then 'Export'
else 'Övrigt'
end as kategori,
count(request), sum((duration+500)/1000) as seconds, sum((duration+500)/1000) * interval '1 second' as hms
FROM weave.wv_statistics
--where date(submitted) = date(now())
group by 1
order by 3 desc;

Browser not supported