A simple statistics view with page hits for this application.
The dynamic action on page 0 handles the page hit logging.

For the package below, you will need this: grant execute on dbms_crypto to tedstruik;

All the hashing of IP addresses is needed to comply with the AVG/GDPR laws. The MAC key (ted_secret_stuff.c_salt) is a secret random string, which I put in another package for obvious reasons.

The IPSTACK_JSON column is for future use, it might be useful to get some info on the IP (before we hash it).

Note 1: For a simpler method of logging page visits, you can also use the method described by Martin Giffy D'Souza, which is discussed on my Apex -> General -> Logs page. That would be a bit easier to implement than this one, but also a bit less flexible.
Note 2: I really should find a way to filter out all the search bots from this list, like Google, Bing, etc. Some (most?) of these pages seem quite popular but that's just because some search bots keep visiting them over and over...
Note 3: In March 2021 my VPS provider OVH had some downtime, and in April my Users tablespace got filled up by accident - so nothing was logged for 10 days in March and after April 9th. And since I already planned to update this package, I have stopped logging for the moment. To be continued.

Region

Identification
	Sequence	50
	Title	Unique Visits per Month
	Type	Chart
Source
	Source Type	NATIVE_JET_CHART
Chart Series
	Series Name	Visits
	Sequence	10
	Data Source Type	SQL Query
	Data Source	select to_char(t.month_inserted, 'YYYY-MM') as month_inserted , t.session_n from ted_p1113_session_month_mv t where t.month_inserted < trunc(sysdate, 'MM') and t.month_inserted < to_date('01-03-2021', 'DD-MM-YYYY') -- ### Temporary Fix order by 1

Region

Identification
	Sequence	60
	Title	Page Visits per Month
	Type	Chart
Source
	Source Type	NATIVE_JET_CHART
	Region Source	select to_char(t.month_inserted, 'YYYY-MM') as month_inserted , trim(t.page_id) || ' - ' || t.page_name as page_descr , t.session_n from ted_p1113_session_page_month_mv t where t.month_inserted < trunc(sysdate, 'MM') and t.month_inserted < to_date('01-03-2021', 'DD-MM-YYYY') -- ### Temporary Fix order by 1 , 2
Chart Series
	Series Name	Visits
	Sequence	10

Table

Table
	Name	ted_access_logs
	DDL	CREATE TABLE "TEDSTRUIK"."TED_ACCESS_LOGS" ( "DATE_INSERTED" DATE NOT NULL ENABLE, "APP_ID" NUMBER NOT NULL ENABLE, "PAGE_ID" NUMBER NOT NULL ENABLE, "APP_SESSION" NUMBER NOT NULL ENABLE, "N" NUMBER NOT NULL ENABLE, "IPSTACK_JSON" CLOB, "IP_ADDRESS_HASHED" RAW(256) NOT NULL ENABLE, "BOT_PK_ID" NUMBER, CONSTRAINT "TED_ACCESS_LOGS_UK" UNIQUE ("DATE_INSERTED", "APP_ID", "PAGE_ID", "APP_SESSION", "IP_ADDRESS_HASHED") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "TED_ACCESS_LOGS_BOTS_FK" FOREIGN KEY ("BOT_PK_ID") REFERENCES "TEDSTRUIK"."TED_ACCESS_LOG_BOTS" ("PK_ID") ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" LOB ("IPSTACK_JSON") STORE AS SECUREFILE ( TABLESPACE "USERS" ENABLE STORAGE IN ROW 4000 CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

View

View
	Name	ted_apex_application_pages_v
	DDL	with iv_list as (select distinct -- needed for page 1069, which is included twice in the menu li.application_id ,to_number(regexp_substr(lie.entry_target, '[^:]+', 1, 2)) as page_id from apex_application_lists li join apex_application_list_entries lie on lie.list_id = li.list_id where li.application_id = 25384 -- hardcoding this makes debugging in Apex Builder (which has app_id 4500) a lot easier and li.list_name = 'Navigation Menu' and nvl(lie.condition_type_code, 'x') <> 'NEVER') select p.application_id ,p.page_id ,p.page_title ,p.page_name ,nvl(pd.created_on, p.created_on) as created_on ,nvl(pd.last_updated_on, p.last_updated_on) as last_updated_on from apex_application_pages p join iv_list on (iv_list.application_id = p.application_id and iv_list.page_id = p.page_id) -- only show pages included in the Navigation Menu left join ted_page_dates pd on (pd.application_id = p.application_id and pd.page_id = p.page_id) where to_number(p.page_id) >= 1000 -- to_number needed to prevent "ORA-01722: invalid number"

Materialized View

Materialized View
	Name	ted_p1113_session_month_mv
	DDL	CREATE MATERIALIZED VIEW "TEDSTRUIK"."TED_P1113_SESSION_MONTH_MV" ("MONTH_INSERTED", "SESSION_N") SEGMENT CREATION IMMEDIATE ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate)+1 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS with h as ( -- my own ip addresses select ted_access_log.hash('82.197.212.38') as h from dual union all select ted_access_log.hash('51.91.96.181') as h from dual ) , p as ( select p1.application_id , p1.page_id , p1.page_name from ted_apex_application_pages_v p1 union all select p2.application_id , p2.page_id , p2.page_name from apex_application_pages p2 where p2.application_id = nvl(v('APP_ID') ,25384) and p2.page_id between 1 and 999 and p2.page_id not in (101) ) , a as ( select t.app_id , trunc(t.date_inserted, 'MM') as month_inserted , count(distinct t.app_session) as session_n from ted_access_logs t where t.ip_address_hashed not in ( select h from h ) and t.date_inserted >= to_date('2018-10-15', 'YYYY-MM-DD') -- after the day I installed the logging packages etc. group by t.app_id , trunc(t.date_inserted, 'MM') ) select a.month_inserted , a.session_n from a

Materialized View

Materialized View
	Name	ted_p1113_session_page_month_mv
	DDL	CREATE MATERIALIZED VIEW "TEDSTRUIK"."TED_P1113_SESSION_PAGE_MONTH_MV" ("PAGE_ID", "PAGE_NAME", "MONTH_INSERTED", "SESSION_N") SEGMENT CREATION IMMEDIATE ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate)+1 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS with h as ( -- my own ip addresses select ted_access_log.hash('82.197.212.38') as h from dual union all select ted_access_log.hash('51.91.96.181') as h from dual ) , p as ( select p1.application_id , p1.page_id , p1.page_name from ted_apex_application_pages_v p1 union all select p2.application_id , p2.page_id , p2.page_name from apex_application_pages p2 where p2.application_id = nvl(v('APP_ID') ,25384) and p2.page_id between 1 and 999 and p2.page_id not in (101) ) , a as ( select t.app_id , t.page_id , trunc(t.date_inserted, 'MM') as month_inserted , count(distinct t.app_session) as session_n from ted_access_logs t where t.ip_address_hashed not in ( select h from h ) and t.date_inserted >= to_date('2018-10-15', 'YYYY-MM-DD') -- after the day I installed the logging packages etc. group by t.app_id , t.page_id , trunc(t.date_inserted, 'MM') ) select p.page_id , p.page_name , a.month_inserted , a.session_n from p , a where p.application_id = a.app_id and p.page_id = a.page_id

Package

Package
	Name	ted_access_log
	Source	package ted_access_log is procedure update_bots; function ip_to_integer(p_ip_address in varchar2) return number deterministic; function bot_pk_id ( p_ip_address in varchar2 ,p_user_agent in varchar2 ) return ted_access_log_bots.pk_id%type; function hash(p_unhashed in varchar2) return raw; procedure log_insert; end ted_access_log;

Package Body

Package Body
	Name	ted_access_log
	Source	package body ted_access_log is procedure update_bots is cursor c_monperrus is select jt.* ,lua.created_by from ted_access_log_user_agents lua ,json_table(lua.list_clob, '$[*]' columns(pattern path '$.pattern')) jt where lua.created_by = 'monperrus' and lua.active_yn = 'Y'; begin -- monperrus -- https://github.com/monperrus/crawler-user-agents?tab=readme-ov-file for r_monperrus in c_monperrus loop begin insert into ted_access_log_bots bots (bot_type ,info_source ,ip_from ,ip_to ,user_agent_regex) values ('Web Crawler' ,r_monperrus.created_by ,null ,null ,r_monperrus.pattern); exception when dup_val_on_index then -- needed to prevent ORA-02292: integrity constraint (TEDSTRUIK.TED_ACCESS_LOGS_BOTS_FK) violated - child record found (to TED_ACCESS_LOGS) null; end; end loop; end update_bots; function ip_to_integer(p_ip_address in varchar2) return number deterministic is l_ip_address varchar2(100) := p_ip_address; l_result number; begin -- apex_workspace_activity_log sometimes shows 2 comma-separated IP address; -- in those cases the first one seems to be the one we need. if instr(l_ip_address, ',') > 0 then l_ip_address := substr(l_ip_address, 1, instr(l_ip_address, ',') - 1); end if; -- NoFormat Start l_result := 16777216 * to_number(substr(l_ip_address,1,instr(l_ip_address,'.',1,1)-1)) + 65536 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,1)+1,instr(l_ip_address,'.',1,2)-instr(l_ip_address,'.',1,1)-1)) + 256 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,2)+1,instr(l_ip_address,'.',1,3)-instr(l_ip_address,'.',1,2)-1)) + to_number(substr(l_ip_address,instr(l_ip_address,'.',1,3)+1)) ; -- NoFormat End return l_result; exception when others then -- this may happen for IP6-only entries return null; end ip_to_integer; function bot_pk_id ( p_ip_address in varchar2 ,p_user_agent in varchar2 ) return ted_access_log_bots.pk_id%type is l_result ted_access_log_bots.pk_id%type; cursor c_bots is select min(bots.pk_id) as bots_pk_id from ted_access_log_bots bots where ip_to_integer(p_ip_address) between ip_to_integer(bots.ip_from) and ip_to_integer(coalesce(bots.ip_to, bots.ip_from)) or regexp_substr(p_user_agent, bots.user_agent_regex) is not null or (p_user_agent is null and bots.user_agent_regex is null and bots.ip_from is null); begin open c_bots; fetch c_bots into l_result; close c_bots; return l_result; exception when others then raise_application_error(-20000, sqlerrm); end bot_pk_id; function hash(p_unhashed in varchar2) return raw is v_hash_raw raw(256); v_charset varchar2(100); begin if p_unhashed is null then -- Needed to prevent "ORA-29261: bad argument" in dbms_crypto.mac return null; end if; select value into v_charset from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; v_hash_raw := dbms_crypto.mac(src => utl_i18n.string_to_raw(data => p_unhashed, dst_charset => v_charset) ,typ => dbms_crypto.hash_sh256 ,key => utl_i18n.string_to_raw(ted_secret_stuff.c_salt)); -- better alternative, that needs no grants on dbms_crypto: /* select standard_hash('p_unhashed' || utl_i18n.string_to_raw(ted_secret_stuff.c_salt)) into v_hash_raw from dual ; */ return v_hash_raw; exception when others then raise_application_error(-20000, sqlerrm); end hash; function hash_check ( p_unhashed in varchar2 ,p_hashed in raw ) return boolean is begin if p_hashed = hash(p_unhashed => p_unhashed) then return true; end if; return false; exception when others then raise_application_error(-20000, sqlerrm); end hash_check; procedure log_insert is pragma autonomous_transaction; l_scope logger_logs.scope%type := $$plsql_unit || '.' || 'log_insert'; l_params logger.tab_param; l_access_logs_row ted_access_logs%rowtype; l_ip_address varchar2(100); l_ip_address_hashed raw(256); begin l_ip_address := nvl(owa_util.get_cgi_env('X-FORWARDED-FOR'), owa_util.get_cgi_env('REMOTE_ADDR')); l_ip_address_hashed := hash(p_unhashed => l_ip_address); l_access_logs_row.date_inserted := trunc(sysdate); l_access_logs_row.app_id := v('APP_ID'); l_access_logs_row.page_id := v('APP_PAGE_ID'); l_access_logs_row.ip_address_hashed := l_ip_address_hashed; l_access_logs_row.app_session := v('APP_SESSION'); l_access_logs_row.n := 1; l_access_logs_row.bot_pk_id := bot_pk_id(p_ip_address => l_ip_address, p_user_agent => owa_util.get_cgi_env('HTTP_USER_AGENT')); begin insert into ted_access_logs values l_access_logs_row; exception when dup_val_on_index then update ted_access_logs a set a.n = a.n + 1 ,a.bot_pk_id = l_access_logs_row.bot_pk_id -- bots filters may have changed, so we always update this where a.date_inserted = l_access_logs_row.date_inserted and a.app_id = l_access_logs_row.app_id and a.page_id = l_access_logs_row.page_id and a.ip_address_hashed = l_access_logs_row.ip_address_hashed and a.app_session = l_access_logs_row.app_session; end; commit; exception -- we don't want to break the entire website if this fails when others then logger.log_error(sqlerrm, l_scope, null, l_params); end log_insert; end ted_access_log;

Dynamic Action

Note: This object is located on page 0

Identification
	Name	ted_access_log
Execution Options
	Sequence	120
When
	Event	Page Load
Advanced
	Event Scope	Static
True Action
Identification
	Action	Execute Server-side Code
Settings
	Code	begin ted_access_log.log_insert; end;
Execution Options
	Sequence	10
	Fire On Initialization	No