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

;

Netezza: concatenate rows into string with XMLAGG()

Note: Same functionality can be achieved creating UDX at Netezza database:

Netezza: create UDX concatenating row values into coma delimited string GROUP_CONCAT

---

Pre-condition:  you should have SQL Toolkit installed (read installation notes here).  

SQL Extensions Toolkit: reference here.

--- Create sample data

create table t1 (num int, color varchar(10 ));

insert into t1 values (1,'red');

insert into t1 values (2,'black');

insert into t1 values (3,'red');

insert into t1 values (4,'yellow');

insert into t1 values (5,'green');

select * from t1 order by 1;

------------------------------------------

--- Simple example

select SQLX..replace (

                     SQLX.. replace(

                                  SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement( 'X',color)))

                     , '<X>','' )

              , '</X>',',' )

from t1;

------------------------------------------

--- Trim trailing comma

select trim (trailing ',' from

                  SQLX..replace(SQLX..replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X',color))), '<X>','' ),'</X>' ,',' )

              )

from t1;

------------------------------------------

--- Experimenting with volume of data to concatenate

select database

       , count(* ) as NUM_OF_TABS

       , length(SQLX..replace (SQLX..replace(SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement( 'X',tablename))), '<X>','' ),'</X>' ,',' )) as STRING_LEN

       , trim(trailing ',' from SQLX..replace(SQLX..replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X',tablename))), '<X>','' ),'</X>' ,',' )) AS CONCAT_STRING

from

       ( select * from _v_table_xdb order by random() limit 1400) as f

group by database ;

select * from _v_table_xdb order by random();

select random ();

Testing the limits of concatenated string: 

Renders "XML buffer overflow" when too much of data (in my case over 1600 records). 

------------------------------------------

/*

ERROR [HY000] ERROR:  0 : XML: Buffer Overflow

 */

---

/* Select user logon activity with list of databases ever accessed (last fiels)  */

SELECT case when (select count (* ) from _V_DSLICE) = 22 then 'DEV'

            when (select count (* ) from _V_DSLICE) = 46 then 'TEST'

            when (select count (* ) from _V_DSLICE) = 92 then 'PROD'

       else 'UNK' end as ENV,

        username,

        U.DISPLAYNAME,

        MAX(LAST_LOGON) as last_logon,

        SUM(ACCESS_COUNT) as total_login_count,

        trim (trailing ', ' from SQLX..replace(SQLX.. replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X' ,dbname))), '<X>', '' ), '</X>' ,',' )) AS DATABASES

FROM

(

     select username, dbname, max( time) AS LAST_LOGON, count(*) AS ACCESS_COUNT

     from HISTDB.HIST."$v_hist_log_events"

     where username not in ('ADMIN4BACKUP','ADMIN' ,'S_NZ_PRODUSER' ,'S_NZ_PRODCOGNOS' ,'HIST' ,'S_NZ_PRODADMIN' ,'TESTSD' ,'TESTSAVO' )

     group by username, dbname

) as f

  JOIN NZDBA. ADMIN.V_ACTIVE_USERS U

ON U.UID = F.username

GROUP BY username, U.DISPLAYNAME

 order by total_login_count desc

 ;

---

Note, in Oracle same string concatenation functionality can be achieved in multiple ways. This post describes Oracle's way doing this.