with tmp_sessions as (select instance_name,user_name,regexp_substr(clnt_ip, '(([1-9]?[0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([1-9]?[0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])',1,1) as ip ,appname from Gv$sessions)select instance_name,user_name,ip,appname,count(*) from tmp_sessions group by instance_name,user_name,ip,appname;
