Netezza: view to monitor user run and logon statistics

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

;

---

Create view DDL.

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

;