Create user activity monitoring views. Views are useful to monitor user logons and Netezza appliance usage:
Create view DDL.
SELECT 'DEV' as ENV
,username AS USERNAME
,u.CREATEDATE AS CREATEDATE
,timezone(MAX(LAST_LOGON), 'UTC','EST' ) as LAST_LOGON
, CURRENT_DATE-timezone(MAX(LAST_LOGON), 'UTC','EST' ) as LAST_LOGON_DAYS
,SUM(ACCESS_COUNT) as TOTAL_LOGIN_COUNT
--- List concatenation here: put all database on one line comma delimited.
--- Read: http://nz2nz.blogspot.com/2016/02/netezza-concatenate-rows-into-string.html
,trim (trailing ', ' from
SQL_EXTENSIONS.. replace
(SQL_EXTENSIONS.. replace
(SQL_EXTENSIONS..XMLserialize
(SQL_EXTENSIONS..XMLagg
(SQL_EXTENSIONS..XMLElement( 'X',dbname))),
'<X>','' )
, '</X>' ,',' )) AS DB_ACCESSED
FROM
(
select username
,dbname
, max( time) AS LAST_LOGON
, count(* ) AS ACCESS_COUNT
from QUERY_HISTORY.."$v_hist_log_events"
where username not like 'ADMIN%' --- not interested in ADMIN** users
and username not like 'ZZ_%' --- not interested in ZZ_** functional users
group by username
, dbname
) as LAST_LOGON_DAYS
left outer join _v_user u using (username)
GROUP BY 1 ,2 ,3
order by TOTAL_LOGIN_COUNT desc
;
SELECT 'DEV' AS ENV
, QP.USERNAME AS USERNAME
, DATE_TRUNC('day' , QP.SUBMITTIME) AS THEDAY
, MIN(QP.SUBMITTIME) AS RUN_FROM
, MAX(QE.FINISHTIME) AS RUN_TO
, COUNT(*) AS QUERY_COUNT
, SUM((QE.FINISHTIME - QP.SUBMITTIME)) AS TOTAL_ELAPSED
, SUM(QE.RESULTROWS) AS TOTAL_ROWS
, SUM(P.RESULTBYTES) AS TOTAL_BYTES
FROM ( QUERY_HISTORY.QHIST_USER."$hist_query_prolog_3" QP
JOIN QUERY_HISTORY.QHIST_USER."$hist_query_epilog_3" QE
USING (NPSID, NPSINSTANCEID, OPID) )
JOIN ( QUERY_HISTORY.QHIST_USER."$hist_session_prolog_3" SP
JOIN QUERY_HISTORY.QHIST_USER."$hist_session_epilog_3" SE
USING (NPSID, NPSINSTANCEID, SESSIONID))
USING (NPSID, NPSINSTANCEID)
LEFT JOIN (
SELECT PP.NPSID
, PP.NPSINSTANCEID
, PP.OPID
, PP.SESSIONID
, SUM(PE.RESULTBYTES) AS RESULTBYTES
, SUM((PP.PREPTIME - PP.SUBMITTIME)) AS QUEUETIME
, SUM((PP.GRATIME - PP.PREPTIME)) AS PREPTIME
, SUM((PP.STARTTIME - PP.GRATIME)) AS GRATIME
, COUNT(PP.PLANID) AS NUMPLANS
, SUM(PP.QCRESTART) AS NUMRESTARTS
FROM (QUERY_HISTORY.QHIST_USER."$hist_plan_prolog_3" PP
JOIN QUERY_HISTORY.QHIST_USER."$hist_plan_epilog_3" PE
USING (NPSID, NPSINSTANCEID, OPID, SESSIONID, PLANID))
GROUP BY PP.NPSID, PP.NPSINSTANCEID, PP.OPID, PP.SESSIONID
) P
USING (NPSID, NPSINSTANCEID, OPID)
WHERE QP.SESSIONID = SP.SESSIONID
AND QE.STATUS = 0
AND QP.SUBMITTIME > current_timestamp - interval '60 days'
AND UPPER(QP.QUERYTEXT) NOT IN ( 'COMMIT','BEGIN' , 'SELECT * FROM DUMMY WHERE 1=2')
AND UPPER(QP.QUERYTEXT) not like 'SHOW %'
AND UPPER(QP.QUERYTEXT) not like 'SET %'
AND UPPER(QP.QUERYTEXT) not like 'SELECT VERSION%'
AND UPPER(QP.QUERYTEXT) not like 'SELECT DB_ENCODING%'
AND UPPER(QP.QUERYTEXT) not like 'SELECT NCHAR_ENCODING%'
AND UPPER(QP.QUERYTEXT) not like 'SELECT CURRENT_SID%'
AND UPPER(QP.QUERYTEXT) not like 'SELECT ''JDBC CLIENT VERSION''%'
/* to be continued based on your system. This xclusion list is to be revised ...*/
GROUP BY ENV, QP.USERNAME, DATE_TRUNC( 'day', QP.SUBMITTIME)
order by THEDAY desc, TOTAL_ELAPSED desc
;
